シート名を日付に従って変更

Question

毎週作成しているシートをVBAで自動作成できるようにしています。
その際シート名を日付に従って変更したいです。

具体的には「予定表0913-0917」です。
※9/13(月)～9/17(金)の予定という意味

そこで下記のコードを書きました。

Sub 新規作成()

Dim M As String '月曜日
Dim F As String '金曜日

M = Format(Date, yyyymmdd)
F = DateAdd("d", 4, M)

ThisWorkbook.Worksheets(1).Copy before:=Worksheets(1)
ThisWorkbook.Worksheets(1).Cells(1, 2).Value = ThisWorkbook.Worksheets(1).Cells(1, 2).Value + 7
ActiveSheet.Name = "予定表(" & M & "～" & F & ")"

これだとエラーが出てしまいます。
※可能であれば0913-0917にしたいですが、上記で9/13に走らせると20210913になることは承知しています。
具体的にどこが悪いか教えて頂けると幸いです。
宜しくお願い致します。

End Sub

Answer 1

Dim M As String '月曜日
Dim F As String '金曜日
Dim N As String 'シート名

M = Format(Date, "mmdd")
F = Format(DateAdd("d", 4, Date), "mmdd")
N = "予定表(" & M & "-" & F & ")"

Comments

[somechan1]

ご回答ありがとうございます。
上記参考に実施すると、一番最後の
ActiveSheet.Name = N
でエラー1004になってしまいます。

Thisworkbook.worksheet(1)などに変更してもエラーになってしまいます。
最終行の書き方で誤った箇所を御指摘頂けると幸いです。

[jinoji]

最初に確認するとしたら、同じシート名が既にあるとかですかね。

[somechan1]

今一度見直した結果無事動かせることが出来ました。
具体的にはF = Format(DateAdd("d", 4, Date), "mmdd")の最後の"が抜けていました。
本当にありがとうございました。

Answer 2

まず、以下ですが、2番目の引数は「"yyyymmdd"」のようにダブルクォーテーションで囲む必要があります。

M = Format(Date, yyyymmdd)

また、変数Mの日付の「4日後」を求めたいのでしょうかれど、この時点で、変数Mには「20210908」のような、日付ではなく「文字」が入っているので、DateAdd関数はエラーになります（3番目の引数には日付を指定しなくてはなりません）。

F = DateAdd("d", 4, M)

ですので、仮にこのプログラムを月曜日に実行するとすると、上記の2行は次のようになります。

M = Format(Date, "yyyymmdd")
F = Format(DateAdd("d", 4, Date), "yyyymmdd")

※変数Fの方もFormat関数で書式を指定している点に注意してください。

なお、ご希望の「0913-0917」にしたいのであれば、上記の「"yyyymmdd"」を2箇所とも「"mmdd"」にしていただければ。

Comments

[somechan1]

ご回答ありがとうございます。
上記参考に実施すると、一番最後の
ActiveSheet.Name = N
でエラー1004になってしまいます。

Thisworkbook.worksheet(1)などに変更してもエラーになってしまいます。
最終行の書き方で誤った箇所を御指摘頂けると幸いです。

[hex309]

ご提示頂いたコードは、以下ですよね。

ActiveSheet.Name = "予定表(" & M & "～" & F & ")"

「ActiveSheet.Name = N」は、私ではなく、jinojiさんのコードを参考にされたのでは？
もとのコードでもエラーになりますか？

[somechan1]

最終行はhex309さんが記載頂いたコードを使用しています。
また今一度hex309さんのアドバイスを参考にコードを記載しなおした結果
無事動かせることが出来ました。

誤りの個所はF = Format(DateAdd("d", 4, Date), "yyyymmdd")
の一番最後の"が抜けていました。

今回は本当にありがとうございました。