エクセルで次のようなのマクロ作成をお願いいたします。

なんとなく原因はわかります。

通常シートを増やすとSheet1、Sheet2・・・とシート名がついていき、オブジェクト名もSheet1、Sheet2・・・とついていきます。

ここでシートの名前をSheet1とSheet2と入れ替えても、オブジェクト名は変わりません。

そんな感じで今の場合はシート名がSheet1だけど、オブジェクト名が違うシートとなっているのでしょう。

それで、コード中のシートの取得をシート名に変えました。

これで、シートの名前がSheet1～Sheet5のシートが５つあればうまく動作すると思います。

Sub Macro1()
    Dim i As Integer
    Dim j As Integer
    Dim r As Integer
    Dim lastRow As Long
    
    With Worksheets("Sheet1")
        lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        
        For j = 1 To 3
            Worksheets("Sheet2").Cells(1, j).Value = .Cells(4, j).Value
            r = 2
            For i = 5 To .Cells(1, j).Value + 4
                Worksheets("Sheet2").Cells(r, j).Value = .Cells(i, j).Value
                r = r + 1
            Next i
            Worksheets("Sheet3").Cells(1, j).Value = .Cells(4, j).Value
            r = 2
            For i = .Cells(1, j).Value + 5 To .Cells(2, j).Value + 4
                Worksheets("Sheet3").Cells(r, j).Value = .Cells(i, j).Value
                r = r + 1
            Next i
            Worksheets("Sheet4").Cells(1, j).Value = .Cells(4, j).Value
            r = 2
            For i = .Cells(2, j).Value + 5 To .Cells(3, j).Value + 4
                Worksheets("Sheet4").Cells(r, j).Value = .Cells(i, j).Value
                r = r + 1
            Next i
            Worksheets("Sheet5").Cells(1, j).Value = .Cells(4, j).Value
            r = 2
            For i = .Cells(3, j).Value + 5 To lastRow
                Worksheets("Sheet5").Cells(r, j).Value = .Cells(i, j).Value
                r = r + 1
            Next i
        Next j
    End With

End Sub