VBAで自動集計（特定セルコピー月ごとに値貼り付け）したい。

いつも大変お世話になっております。
VBAで自動集計できるようなシステムを作っています。手助けを御願いします。

やりたいこと
B2の値が月末の時５行目から１０行目をそれぞれの月の部分へ自動で値貼り付けしたい。（今作ったので動作はしているようですがどうでしょうか。）
すべての編集が終わってファイルを閉じたときに自動で動作をするようにしたい。
うるう年対応したいのと、一度作成すると毎年使えるものを作りたいです。（現在年はA1セルから引っ張っているのでA1セルを年度が替わるごとに手入力しないといけない。月末データの集計は毎月第３営業日に確定するため、NOWなどの関数は利用できない。）

条件セルの設定
A１セルより年を参照しています。
4列目の月のところは【 0”月”】とセルの書式設定をしています。

もっと簡潔な方法があればご教授お願いします。
素人作業で申し訳ありませんがよろしくお願いいたします。

質問者からの補足コメント

• 引き続きありがとうございます。それでは答えが出なかったため、新たにvbaにて自動化しようとし奮闘してます。
  
  条件セルの設定については、日にちの参照先です。
  Ｂ2セルがあればＡ1は不要→Q列に末尾マスターを作成していてその参照先がa1年4行月としています。
  
  Ｂ2セルは報告書の数値をセル参照で引っ張ってきます。
  
  月表示の部分の解釈はご認識のとおりです！

    補足日時：2023/06/25 17:29

• 

  仮定部分についてですが、
  ※ B2の値が月末でない場合には何もしません。
  ⇒ 例えば、年末(12月31日)であっても、ブックを開き、閉じなければ処理は行われません。
  報告日とありますが、実績集計日と置き換えてください。なので、翌月第3営業日にデータ作成がされたものが送られてくる仕組みです。
  なので、B2セルが月末になっている状態でブックを閉じるのは月初第3営業日ですので問題ない認識です。
  
  最後に「ThisWorkbook」モジュールと標準モジュールとして追加したモジュールとはどう異なるのでしょうか？
  ほんと、基本のきが分からず申しわけありません。ご教授お願いします！

    補足日時：2023/06/25 17:29

• 

  ありがとうございます。
  ご指示通り、コピペしましたが、何にも機能しませんでした・・・・・・。どうすればいいでしょうか。

  No.1の回答に寄せられた補足コメントです。 補足日時：2023/06/25 20:39

• 

  エラーメッセージ
  〇月に該当する列がない
  だそうです。
  
  〇は月によって替わります。

  No.2の回答に寄せられた補足コメントです。 補足日時：2023/06/26 02:27

No.6 回答者： fujillin 回答日時： 2023/06/26 17:14

No2です。

＞〇月に該当する列がない
＞だそうです。
＞〇は月によって替わります
表示される「〇月」は正しいのですよね？
それで検索すると、該当する列が見つからないというメッセージ通りの意味です。
（多分、タイトル行の表示内容が想定の解釈と違うものになっているのでしょう）
文章での確認しかできないので、そちらのシートの実態は把握できかねますので。

面倒なので、4月はＤ列、5月はＥ列・・と決め打ちして転記すれば良いと思いましたが、既に、他の方がその方式での回答なさっていますので、そちらをご利用ください。

No.5 回答者： tatsumaru77 回答日時： 2023/06/26 16:03

No3,No4です。

No4は誤って投稿したものです。No4は無視してください。

No.4 回答者： tatsumaru77 回答日時： 2023/06/26 12:23

Thisworkbookへ下記のマクロを登録してください。

（添付図参照）

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wd As Variant
Dim ws As Worksheet
Dim wcol As Long
Dim wmonth As Long
Set ws = Worksheets("Sheet1")
wd = ws.Cells(2, 2).Value
If IsDate(wd) = False Then
MsgBox ("B2の値不正")
Exit Sub
End If
If wd <> Application.EoMonth(wd, 0) Then
MsgBox ("月末日でない")
Exit Sub
End If
wmonth = Month(wd)
If wmonth < 4 Then wmonth = wmonth + 12
wcol = wmonth
ws.Cells(5, wcol).Resize(6, 1).Value = ws.Cells(5, 2).Resize(6, 1).Value
End Sub

使用時の注意事項
B2が月末日でないとき、”月末日でない”と表示されます。
この表示が不要な場合は、当該箇所をコメントアウトしてください。

No.3 回答者： tatsumaru77 回答日時： 2023/06/26 11:45

Thisworkbookのモジュールとして以下のマクロを

登録してください。（下図参照）

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wd As Variant
Dim ws As Worksheet
Dim wcol As Long
Dim wmonth As Long
Set ws = Worksheets("Sheet1")
wd = ws.Cells(2, 2).Value
If IsDate(wd) = False Then
MsgBox ("B2の値不正")
Exit Sub
End If
If wd <> Application.EoMonth(wd, 0) Then
MsgBox ("月末日でない")
Exit Sub
End If
wmonth = Month(wd)
If wmonth < 4 Then wmonth = wmonth + 12
wcol = wmonth
ws.Cells(5, wcol).Resize(6, 1).Value = ws.Cells(5, 2).Resize(6, 1).Value
End Sub

ーーーーーーーーーーーーーーーーーーーー
注意事項
Sheet1のB２が日付でない場合、”B２の値不正”　・・・・①
Sheet1のB２が月末日でない場合、”月末日でない”・・・・②
のメッセージが表示されます。
特に②のメッセージは、通常の運用時にも発生しますので、動作確認がおわりましたら、②のメッセージを出力する箇所をコメントアウトしておいてください。
必ず、Thisworkbookに作成してください。

No.2 回答者： fujillin 回答日時： 2023/06/25 21:52

No1です。

＞ご指示通り、コピペしましたが、何にも機能しませんでした・
＞どうすればいいでしょうか。
何をどうしたらどうなったのか、全くわかりませんけれど・・
動作しているのかしていないのかすらも不明です。

とりあえず、以下に変えれば、何らかのメッセージが表示されると思います。
（メッセージのところが想定した条件とは異なっているところかと）
何も表示されずかつ実行されなければ、設置方法等が違っている可能性が高いです。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim d, f
With Worksheets("Sheet1")
d = .Cells(2, 2)
If Not IsDate(d) Then
MsgBox "セル値が日付ではない"
Exit Sub
End If
If d <> Application.EoMonth(d, 0) Then
MsgBox "月末日ではない"
Exit Sub
End If
Set f = .Range("D4:O4").Find(Month(d) & "月", LookAt:=xlWhole)
If f Is Nothing Then
MsgBox Month(d) & "月に該当する列が存在しない"
Exit Sub
End If
.Cells(5, f.Column).Resize(6).Value = .Range("B5:B10").Value
End With
End Sub

No.1 回答者： fujillin 回答日時： 2023/06/25 17:10

こんにちは

これ（↓）のような気もしますが、条件がいろいろと違っているような・・？
https://oshiete.goo.ne.jp/qa/13504173.html


とりあえず、こちらのご質問文の通りに解釈するなら・・
＞ファイルを閉じたときに自動で動作をするようにしたい。
マクロ有効ブックで良いのなら、Workbook_BeforeCloseのイベントを利用することで可能です。

＞条件セルの設定
＞A１セルより年を参照しています。
「条件セル」と言うのがどのセルのことか不明です。
Ｂ2セルがシリアル値（＝エクセルの日付型の値）であるなら、Ａ1セルは不要と思いますが？
それとも、Ｂ2セルの値がＡ1セルを参照しているってことでしょうか？
もしそうなら、年以外の部分は何を参照しているのかが不明ですけれど・・

＞4列目の月のところは【 0”月”】とセルの書式設定をしています
4行目のことだと解釈しましたが・・
月表示の部分は、シリアル値ではなく数値の1～12が入力されていて、書式設定で「〇月」のように表示しているものと解釈しました。


ご質問文の通りの処理とするなら、以下で可能と思います。
※ 対象とするシートはSheet1と仮定しています。
※ B2セルの値はシリアル値と仮定しています。
※ 4行目の「月」表示はシリアル値ではなく、上記の通りと仮定しています。
※ B2の値が月末でない場合には何もしません。
⇒ 例えば、年末(12月31日)であっても、ブックを開き、閉じなければ処理は行われません。
　（B2セルが月末になっている状態でブックを閉じる必要があります）

当該ブックの「ThisWorkbook」モジュールに以下をコピペ。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim d, f
With Worksheets("Sheet1")
d = .Cells(2, 2)
If Not IsDate(d) Then Exit Sub
If d <> Application.EoMonth(d, 0) Then Exit Sub
Set f = .Range("D4:O4").Find(Month(d) & "月", LookAt:=xlWhole)
If f Is Nothing Then Exit Sub
.Cells(5, f.Column).Resize(6).Value = .Range("B5:B10").Value
End With
End Sub