日付のセルを複数行コピーする

先日、「日付のセルを複数行コピーする」という質問は練習ではできましたが、実際の作業では「実行時エラー’9’　インデックスが有効範囲にありません。」が表示されます。
実際の作業シートは
“みどり”　　“FAX”　　“み”　　“ど”　　“り”　“sheet1” の5枚のシートがあります。
“みどり”のシート（B7）に日付を入れます。“み”、“ど”、“り”のシートの（A2)にそれぞれ同じ日付が3行コピーする。毎回同じことを繰り替えすので上書きせずに、最終行の下にコピーされるようにしたいと思っています。

下記の回答で練習はできました。
Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
　Dim wS As Worksheet
　　Set wS = Worksheets("Sheet2")
　　　With Target
　　　　If .Address = "$A$1" Then
　　　　　If .Value <> "" And IsDate(.Value) Then
　　　　　　wS.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(3).Value = .Value
　　　　　End If
　　　　End If
　　　End With
End Sub '//この行まで//

実際の作業シートではどのようにすればいいでしょうか？
調べて色々やってみましたが、ますますわからなくなってしまいました。
コピーするシートが、“み”1枚だけの時と ,“み”、“ど”、“り”に同時に3枚コピーする方法を教えていただけますでしょうか？よろしくお願いいたします。

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

• 

  早々にありがとうございます。
  言い忘れましたが日付の（B7)ですがBCDEFの結合セルです。
  
  実行しましたがシートモジュールに以下のとこに黄色のラインがついています。
  Worksheets(myAry(k)).Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(3).Value = .Value

  No.1の回答に寄せられた補足コメントです。 補足日時：2020/02/14 09:24

• 

  “みどり”シートは、案内用で印刷のマクロが組み込んであります。
  “FAX”シートは、 “み”の組、“ど”の組、“り”の組の表があり、優勝から3位までの順位と氏名と点数を入力します。このシートにはFAX用の印刷と各組への転送と上書き保存のマクロが組み込んであります。転送のマクロは、み、ど、り、の各シートにそれぞれ問題なくコピーされています。
  A列に大会日を入力したいのですが、その方法を教えていただきましたが、エラーが出てしまいました。「3行コピー」とは優勝から3位までは3行なので3行同時にとなりました。
  うまく説明もできずに申し訳ありません。

    補足日時：2020/02/14 16:52

• 

  質問です。
  これは Module 1 の End Subの下にコピーすればいいですか？
  
  Sub 日付セット(日付 As Date)
  
  Dim 対象シート As Worksheet
  　For Each 対象シート In Worksheets
  　　Select Case 対象シート.Name
  　　　Case "み", "ど", "り"
  　　　　対象シート.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(3).Value = 日付
  　　End Select
  　Next
  
  End Sub

    補足日時：2020/02/14 20:15

• 残念できませんでした。
  “みどり”　シートの日付を入力したら
  “み”　シートの　A2~A4　同じ日付が入力される
  “ど”　シートの　A2~A4　　〃
  “り”　シートの　A2~A4　　〃
  
  次からは上書きにならないように最終行の下にコピー
  “み”　シートの　A5~A7　同じ日付が入力される
  “ど”　シートの　A5~A7　　〃
  “り”　シートの　A2~A4　　〃
  このような感じです。

    補足日時：2020/02/14 21:29

No.10 ベストアンサー 回答者： GooUserラック 回答日時： 2020/02/14 19:09

「（A2)にそれぞれ同じ日付が3行コピーする。

」とは「（A2)～(A4)の3行にそれぞれ同じ日付をコピーする。」という事ですね？それでしたら以下のような感じではいかがですか？

☆「みどり」シートモジュールへ

Private Sub Worksheet_Change(ByVal Target As Range)

　Select Case Target.Address
　　Case "$B$7"
　　　If IsDate(Target.Value) Then Call 日付セット(Target.Value)
　End Select

End Sub

☆「標準モジュール」の「Module1」などへ

Sub 日付セット(日付 As Date)

Dim 対象シート As Worksheet
　For Each 対象シート In Worksheets
　　Select Case 対象シート.Name
　　　Case "み", "ど", "り"
　　　　対象シート.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(3).Value = 日付
　　End Select
　Next

End Sub

No.11 回答者： GooUserラック 回答日時： 2020/02/14 20:24

前回提示したものの差し替えです。

他のマクロに対してでしたらその通りです。

この回答へのお礼

「日付をコピーして、3か所に一つづつ貼り付け、3か所を一度にコピーしそれを各シートに貼り付ける」というマクロを作り直してみました。
まだ簡単な方法があると思いますが、とりあえず目的は達成できました。
本当にありがとうございました。

お礼日時：2020/02/15 11:09

No.9 回答者： GooUserラック 回答日時： 2020/02/14 11:43

tom04 さんへ

何度かは「myAry = Array("み", "ど", "り") '//←ココで対象シート名を羅列しておく//」でエラーが出たんですよ。でもなぜかでなくなりました。
元々が「Excel XP」で作ったものを、変換したせいかなぁ？と思ってそちらでテストしてもエラーしないんですよね。何なんでしょうね？
どういう場面がダメで、どういう事が使えるのか全く分からなくなりました。

No.8 回答者： tom04 回答日時： 2020/02/14 11:23

この場をお借りして・・・

GooUserラックさんへ
あっ！そちらでもエラーになってしまいましたか？

手元のExcel（2010）で確認した場合、エラーは出なかったので、投稿したのですが。
どうも失礼しました。

＞シートモジュールで他のシートの操作は出来ないはずなのでエラーになる筈なんだけどなぁ？

確かにシートモジュールで別シートのデータ消去などの場合はエラーになった経験がありますが、
単にデータ代入に関してはエラーになったことがないので、そのまま投稿しました。

それでもエラーになるのであれば、GooUserラックさんが回答されているように
標準モジュールにコードを記載し、それをシートモジュールで呼び出した方がいいのかもしれませんね。m(_ _)m

No.7 回答者： GooUserラック 回答日時： 2020/02/14 11:21

No.6 の訂正

なぜか何度かテストしたらエラーが出なくなりました。
あれ？判らなくなりました。

No.6 回答者： GooUserラック 回答日時： 2020/02/14 11:12

tom04 さんへ

こちらでもエラーが出ますね
シートモジュールで他のシートの操作は出来ないはずなのでエラーになる筈なんだけどなぁ？

ちなみにこちらの環境は Windows 10 、Office Home and Business Premium(Excel 2019 32Bit) 相当です

No.5 回答者： tom04 回答日時： 2020/02/14 10:43

No.1です。

＞日付の（B7)ですがBCDEFの結合セルです。

「みどり」シートのB7～F7セルは結合されていて、そのセルにシリアル値を入力！
というコトですね？

今こちらでセル結合後確認してみましたが、ちゃんと動作しました。

＞以下のとこに黄色のラインがついています。

もしかして、「み」・「ど」・「り」のいずれかのSheetが存在しない！ってコトはないでしょうか？

こちらで考えられるのはこの程度ですが・・・m(_ _)m

この回答へのお礼

「日付をコピーして、3か所に一つづつ貼り付け、3か所を一度にコピーしそれを各シートに貼り付ける」というマクロを作り直してみました。
まだ簡単な方法があると思いますが、とりあえず目的は達成できました。
本当にありがとうございました。

お礼日時：2020/02/15 11:11

No.4 回答者： GooUserラック 回答日時： 2020/02/14 10:24

No.3 補足

・「Select Case」～「End Select」を多用しましたが、これは拡張するときに楽なようにです。
・「Worksheet_Change」イベントモジュールには、そのシートでの処理以外は書かないようにするのがコツです。
・「Sub 日付セット(日付 As Date)」のように引数を持つとマクロのリストに載りません。なおマクロのリストに載せたくないが引数が無い場合は引数は「(Optional ○○ As ○○)」のようにすると良いと思います。

No.3 回答者： GooUserラック 回答日時： 2020/02/14 10:14

「3行コピー」が良く判りませんが以下のような物はいかがですか？

☆「みどり」シートモジュールへ

Private Sub Worksheet_Change(ByVal Target As Range)

　Select Case Target.Address
　　Case "$B$7"
　　　If IsDate(Target.Value) Then Call 日付セット(Target.Value)
　End Select

End Sub

☆「標準モジュール」の「Module1」などへ

Sub 日付セット(日付 As Date)

Dim 対象シート As Worksheet
　For Each 対象シート In Worksheets
　　Select Case 対象シート.Name
　　　Case "み", "ど", "り"
　　　　対象シート.Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Value = 日付
　　End Select
　Next

End Sub

No.2 回答者： GooUserラック 回答日時： 2020/02/14 08:56

① “みどり”のシート（B7）に日付が入れば、それに対してマクロが対応して処理を行う。

という事で良いのでしょうか？
②「“み”、“ど”、“り”のシートの（A2)にそれぞれ同じ日付が3行コピーする。」の「3行コピー」はどういう事でしょうか？

エラーについてですが、多分以下のようなことだと思います。
「Private Sub Worksheet_Change(ByVal Target As Range)」～「End Sub」には対象以外のシートの処理を書き込むことは出来ません。
他のシートへの処理も行いたいならば、その部分は標準モジュールに作成し「Call ○○」のように呼び出さないといけません。