【簡単・業務効率化】Excel VBAを使って1か月分のシートを作成・製造実績・結果まとめを行う(indirect関数）

Sub 連続したシート自動作成()
Dim ws As Worksheet
Dim Months As Date
Dim Mydate As Date
Dim i As Long
Dim dFirst As Date
Dim dlast As Date
Dim drest
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

    '最初のシートを変数格納
    Set ws = ThisWorkbook.Worksheets("原紙")
    '最初の日付
    Mydate = ws.Range("A4")
    '一か月が何日間あるか計算
    Months = DateAdd("m", 1, Mydate)
    dFirst = Format(Months, "yyyy/mm/01")
    dlast = dFirst - 1
    drest = dFirst - Mydate
    '一か月の日数ループ
    For i = 1 To drest
        ws.Copy Before:=Sheets(Worksheets.Count)
        ActiveSheet.Name = Right(Format(Mydate, "yyyymmdd"), 2) & "昼"
        ActiveSheet.Range("A4") = Format(Mydate, "yyyy/mm/dd")
        Range("G4") = "昼"

        ws.Copy Before:=Sheets(Worksheets.Count)
        ActiveSheet.Name = Right(Format(Mydate, "yyyymmdd"), 2) & "夜"
        ActiveSheet.Range("A4") = Format(Mydate, "yyyy/mm/dd")
        Range("G4") = "夜"
        
        Range("G4").Interior.Color = RGB(0, 0, 255)

        Mydate = Mydate + 1
    Next i

    MsgBox "完了"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub