Let's Talk Excel

Copy Excel Sheet Print Settings To all Sheets

Imagine having many Sheets in a workbook. Now you would like all of them to print in a same way. Let’s say with the same header and footer. Many people do this by selecting all the sheets and applying the settings, but sometimes the selection itself can be a hassle if the number of Sheets is very large. The VBA code bellow will allow you to copy the print settings of the active Sheet to all other Sheets in a Workbook with one click. Simple and very useful!

Sub XET_CopyCurrentPageSetup()
        s = ""
        With ActiveSheet.PageSetup
            
            ps_LeftHeader = .LeftHeader
            ps_CenterHeader = .CenterHeader
            ps_RightHeader = .RightHeader
            ps_LeftFooter = .LeftFooter
            ps_CenterFooter = .CenterFooter
            ps_RightFooter = .RightFooter
            ps_PrintTitleRows = .PrintTitleRows
            ps_PrintArea = .PrintArea
            ps_LeftMargin = .LeftMargin
            ps_RightMargin = .RightMargin
            ps_TopMargin = .TopMargin
            ps_BottomMargin = .BottomMargin
            ps_Orientation = .Orientation
            ps_Zoom = .Zoom
            ps_FitToPagesWide = .FitToPagesWide
            ps_FitToPagesTall = .FitToPagesTall
            ps_HeaderMargin = .HeaderMargin
            ps_FooterMargin = .FooterMargin
            ps_PrintHeadings = .PrintHeadings
            ps_PrintGridlines = .PrintGridlines
            ps_PrintComments = .PrintComments
            ps_PrintQuality = .PrintQuality
            ps_CenterHorizontally = .CenterHorizontally
            ps_CenterVertically = .CenterVertically
            ps_Orientation = .Orientation
            ps_Draft = .Draft
            ps_PaperSize = .PaperSize
            ps_FirstPageNumber = .FirstPageNumber
            ps_Order = .Order
            ps_BlackAndWhite = .BlackAndWhite
            ps_Zoom = .Zoom
            ps_PrintErrors = .PrintErrors
            ps_OddAndEvenPagesHeaderFooter = .OddAndEvenPagesHeaderFooter
            ps_DifferentFirstPageHeaderFooter = .DifferentFirstPageHeaderFooter
            ps_ScaleWithDocHeaderFooter = .ScaleWithDocHeaderFooter
            ps_AlignMarginsHeaderFooter = .AlignMarginsHeaderFooter
            ps_EvenPageLeftHeaderText = .EvenPage.LeftHeader.Text
            ps_EvenPageCenterHeaderText = .EvenPage.CenterHeader.Text
            ps_EvenPageRightHeaderText = .EvenPage.RightHeader.Text
            ps_EvenPageLeftFooterText = .EvenPage.LeftFooter.Text
            ps_EvenPageCenterFooterText = .EvenPage.CenterFooter.Text
            ps_EvenPageRightFooterText = .EvenPage.RightFooter.Text
            ps_FirstPageLeftHeaderText = .FirstPage.LeftHeader.Text
            ps_FirstPageCenterHeaderText = .FirstPage.CenterHeader.Text
            ps_FirstPageRightHeaderText = .FirstPage.RightHeader.Text
            ps_FirstPageLeftFooterText = .FirstPage.LeftFooter.Text
            ps_FirstPageCenterFooterText = .FirstPage.CenterFooter.Text
            ps_FirstPageRightFooterText = .FirstPage.RightFooter.Text
        End With

        For Each s In Worksheets
            If s.Name <> ActiveSheet.Name Then
                With s.PageSetup
                    .LeftHeader = ps_LeftHeader
                    .CenterHeader = ps_CenterHeader
                    .RightHeader = ps_RightHeader
                    .LeftFooter = ps_LeftFooter
                    .CenterFooter = ps_CenterFooter
                    .RightFooter = ps_RightFooter
                    .PrintTitleRows = ps_PrintTitleRows
                    .PrintArea = ps_PrintArea
                    .LeftMargin = ps_LeftMargin
                    .RightMargin = ps_RightMargin
                    .TopMargin = ps_TopMargin
                    .BottomMargin = ps_BottomMargin
                    .Orientation = ps_Orientation
                    .Zoom = ps_Zoom
                    .FitToPagesWide = ps_FitToPagesWide
                    .FitToPagesTall = ps_FitToPagesTall
                    .HeaderMargin = ps_HeaderMargin
                    .FooterMargin = ps_FooterMargin
                    .PrintHeadings = ps_PrintHeadings
                    .PrintGridlines = ps_PrintGridlines
                    .PrintComments = ps_PrintComments
                    .PrintQuality = ps_PrintQuality
                    .CenterHorizontally = ps_CenterHorizontally
                    .CenterVertically = ps_CenterVertically
                    .Orientation = ps_Orientation
                    .Draft = ps_Draft
                    .PaperSize = ps_PaperSize
                    .FirstPageNumber = ps_FirstPageNumber
                    .Order = ps_Order
                    .BlackAndWhite = ps_BlackAndWhite
                    .Zoom = ps_Zoom
                    .PrintErrors = ps_PrintErrors
                    .OddAndEvenPagesHeaderFooter = ps_OddAndEvenPagesHeaderFooter
                    .DifferentFirstPageHeaderFooter = ps_DifferentFirstPageHeaderFooter
                    .ScaleWithDocHeaderFooter = ps_ScaleWithDocHeaderFooter
                    .AlignMarginsHeaderFooter = ps_AlignMarginsHeaderFooter
                    .EvenPage.LeftHeader.Text = ps_EvenPageLeftHeaderText
                    .EvenPage.CenterHeader.Text = ps_EvenPageCenterHeaderText
                    .EvenPage.RightHeader.Text = ps_EvenPageRightHeaderText
                    .EvenPage.LeftFooter.Text = ps_EvenPageLeftFooterText
                    .EvenPage.CenterFooter.Text = ps_EvenPageCenterFooterText
                    .EvenPage.RightFooter.Text = ps_EvenPageRightFooterText
                    .FirstPage.LeftHeader.Text = ps_FirstPageLeftHeaderText
                    .FirstPage.CenterHeader.Text = ps_FirstPageCenterHeaderText
                    .FirstPage.RightHeader.Text = ps_FirstPageRightHeaderText
                    .FirstPage.LeftFooter.Text = ps_FirstPageLeftFooterText
                    .FirstPage.CenterFooter.Text = ps_FirstPageCenterFooterText
                    .FirstPage.RightFooter.Text = ps_FirstPageRightFooterText
                End With
            End If
        Next
End Sub

This Code can also be found in an Xnet Excel Tools AddIn for Excel which is free and can be downloaded here.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: