スキルアップ・問題解決なら、モーグにおまかせ！

ヘッダーの設定は、すべてのページ設定属性(左余白、下余白、用紙サイズなど)が、プロパティとして含まれているPageSetupオブジェクトを使用します。
次のサンプルマクロは、アクティブシートのヘッダーを設定します。

Sub Sample1()
    With ActiveSheet.PageSetup
        .LeftHeader = "左"
        .CenterHeader = "真ん中"
        .RightHeader = "右"
    End With
End Sub

次のサンプルマクロは、全てのシートのヘッダーを設定します。

Sub Sample2()
    Dim objSht As Object
    Dim i As Integer

    i = 1
    For Each objSht In Worksheets
        With objSht.PageSetup
            .LeftHeader = i & "枚目の左"
            .CenterHeader = i & "枚目の真ん中"
            .RightHeader = i & "枚目の右"
        End With
        i = i + 1
    Next

End Sub