ページ設定をコピーする

あるシートのページ設定を
他のシートにも設定したいときの方法について見ていきます。

セルの書式設定のように
コピーやペーストのメソッドは見つからなかったので，
コピー元のページ設定に関するプロパティの値を
コピー先のプロパティに指定することで
ページ設定をコピーしていきます。

下のコードを実行する前に，
シート名「ページ設定」にページ設定をしておきます。

「ページ設定」シートのページ設定を
アクティブシートにコピーします。

ヘッダー・フッターに使用する画像の
Filenameプロパティはxlsx形式では
画像データを指定してブックを閉じるまでは保持されます。

一旦閉じたブックを開くと
保存場所や拡張子は保持されていないので
変数img_pathに画像の保存場所，
変数img_exで拡張子を指定しておきます。

次のコードはヘッダー・フッターに画像を設定したら
一旦ブックを保存して閉じて，再度開いてから実行して下さい。

Sub macro20200417a()
'ページ設定のコピー

    Dim sh_name As String
    Dim sh_ps As Object
    Dim img_path As String
    Dim img_ex As String
   
    img_path = "C:\Users\Username\img"
    img_ex = ".png"

    sh_name = "ページ設定"
    img_path = img_path & "\"
    Set sh_ps = Sheets(sh_name).PageSetup
   
    With ActiveSheet.PageSetup
        .PrintTitleRows = sh_ps.PrintTitleRows
        .PrintTitleColumns = sh_ps.PrintTitleColumns
    End With
    Application.PrintCommunication = True
   
    ActiveSheet.PageSetup.PrintArea = ""

    With ActiveSheet.PageSetup
        .LeftHeader = sh_ps.LeftHeader
        .CenterHeader = sh_ps.CenterHeader
        .RightHeader = sh_ps.RightHeader
        .LeftFooter = sh_ps.LeftFooter
        .CenterFooter = sh_ps.CenterFooter
        .RightFooter = sh_ps.RightFooter
        .LeftMargin = sh_ps.LeftMargin
        .RightMargin = sh_ps.RightMargin
        .TopMargin = sh_ps.TopMargin
        .BottomMargin = sh_ps.BottomMargin
        .HeaderMargin = sh_ps.HeaderMargin
        .FooterMargin = sh_ps.FooterMargin
        .PrintHeadings = sh_ps.PrintHeadings
        .PrintGridlines = sh_ps.PrintGridlines
        .PrintComments = sh_ps.PrintComments
        .CenterHorizontally = sh_ps.CenterHorizontally
        .CenterVertically = sh_ps.CenterVertically
        .Orientation = sh_ps.Orientation
        .Draft = sh_ps.Draft
        .PaperSize = sh_ps.PaperSize
        .FirstPageNumber = sh_ps.FirstPageNumber
        .order = sh_ps.order
        .BlackAndWhite = sh_ps.BlackAndWhite
        .Zoom = sh_ps.Zoom
        .PrintErrors = sh_ps.PrintErrors
        .OddAndEvenPagesHeaderFooter = sh_ps.OddAndEvenPagesHeaderFooter
        .DifferentFirstPageHeaderFooter = sh_ps.DifferentFirstPageHeaderFooter
        .ScaleWithDocHeaderFooter = sh_ps.ScaleWithDocHeaderFooter
        .AlignMarginsHeaderFooter = sh_ps.AlignMarginsHeaderFooter
        .EvenPage.LeftHeader.Text = sh_ps.EvenPage.LeftHeader.Text
        .EvenPage.CenterHeader.Text = sh_ps.EvenPage.CenterHeader.Text
        .EvenPage.RightHeader.Text = sh_ps.EvenPage.RightHeader.Text
        .EvenPage.LeftFooter.Text = sh_ps.EvenPage.LeftFooter.Text
        .EvenPage.CenterFooter.Text = sh_ps.EvenPage.CenterFooter.Text
        .EvenPage.RightFooter.Text = sh_ps.EvenPage.RightFooter.Text
        .FirstPage.LeftHeader.Text = sh_ps.FirstPage.LeftHeader.Text
        .FirstPage.CenterHeader.Text = sh_ps.FirstPage.CenterHeader.Text
        .FirstPage.RightHeader.Text = sh_ps.FirstPage.RightHeader.Text
        .FirstPage.LeftFooter.Text = sh_ps.FirstPage.LeftFooter.Text
        .FirstPage.CenterFooter.Text = sh_ps.FirstPage.CenterFooter.Text
        .FirstPage.RightFooter.Text = sh_ps.FirstPage.RightFooter.Text
    End With
   
    '通常・奇数ページのヘッダー画像の設定
    Set sh_ps = Sheets(sh_name).PageSetup.LeftHeaderPicture
    With ActiveSheet.PageSetup.LeftHeaderPicture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.CenterHeaderPicture
    With ActiveSheet.PageSetup.CenterHeaderPicture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.RightHeaderPicture
    With ActiveSheet.PageSetup.RightHeaderPicture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
   
    Set sh_ps = Sheets(sh_name).PageSetup.LeftFooterPicture
    With ActiveSheet.PageSetup.LeftFooterPicture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.CenterFooterPicture
    With ActiveSheet.PageSetup.CenterFooterPicture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.RightFooterPicture
    With ActiveSheet.PageSetup.RightFooterPicture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
   
    '先頭ページのヘッダー画像の設定
    Set sh_ps = Sheets(sh_name).PageSetup.FirstPage.LeftHeader.Picture
    With ActiveSheet.PageSetup.FirstPage.LeftHeader.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.FirstPage.CenterHeader.Picture
    With ActiveSheet.PageSetup.FirstPage.CenterHeader.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.FirstPage.RightHeader.Picture
    With ActiveSheet.PageSetup.FirstPage.RightHeader.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.FirstPage.LeftFooter.Picture
    With ActiveSheet.PageSetup.FirstPage.LeftFooter.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.FirstPage.CenterFooter.Picture
    With ActiveSheet.PageSetup.FirstPage.CenterFooter.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.FirstPage.RightFooter.Picture
    With ActiveSheet.PageSetup.FirstPage.RightFooter.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
   
    '偶数ページのヘッダー画像の設定
    Set sh_ps = Sheets(sh_name).PageSetup.EvenPage.LeftHeader.Picture
    With ActiveSheet.PageSetup.EvenPage.LeftHeader.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.EvenPage.CenterHeader.Picture
    With ActiveSheet.PageSetup.EvenPage.CenterHeader.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.EvenPage.RightHeader.Picture
    With ActiveSheet.PageSetup.EvenPage.RightHeader.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.EvenPage.LeftFooter.Picture
    With ActiveSheet.PageSetup.EvenPage.LeftFooter.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.EvenPage.CenterFooter.Picture
    With ActiveSheet.PageSetup.EvenPage.CenterFooter.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
    Set sh_ps = Sheets(sh_name).PageSetup.EvenPage.RightFooter.Picture
    With ActiveSheet.PageSetup.EvenPage.RightFooter.Picture
        .Filename = img_path & sh_ps.Filename & img_ex
        If .Filename <> "" Then
            .LockAspectRatio = sh_ps.LockAspectRatio
            .Height = sh_ps.Height
            .Width = sh_ps.Width
            .Brightness = sh_ps.Brightness
            .Contrast = sh_ps.Contrast
            .ColorType = sh_ps.ColorType
            .CropBottom = sh_ps.CropBottom
            .CropLeft = sh_ps.CropLeft
            .CropRight = sh_ps.CropRight
            .CropTop = sh_ps.CropTop
        End If
    End With
   
End Sub

設定内容の詳細については、
以下の記事を参照してください。
記事「ヘッダー・フッターの設定」
記事「ヘッダー・フッターのフォント設定」
記事「ヘッダー・フッターに画像を表示させる」
記事「先頭ページ，奇数・偶数ページのヘッダー・フッターの設定」

使用Ver：Excel For Office365