Forum Discussion

AprilW's avatar
AprilW
Copper Contributor
Jul 08, 2021

cloning headers & footers in excel

I am adding some pages to a workbook, I don't want to manually type out all the information I have in the footers & the headers - how do I copy those settings from existing pages to the new pages?

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    AprilW 

    Usually you create your headers and footers separately for each sheet.

    But you can simplify your work :))

     

    So before inserting the header and footer, you can group the worksheets

    (click on the tab of the 1st worksheet

    - hold down the Shift key

    - click on the tab of the last worksheet).

    [Group] now appears in the title bar.

    All settings and changes you make now will be transferred to the other worksheets.

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

    • AprilW's avatar
      AprilW
      Copper Contributor

      NikolinoDE thanks, that is what I have done in the past, but that involves recreating them again, and each time it is the same information, just a pain in my butt

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor

        AprilW 

        Maybe a try with VBA :))

         

         

        Option Explicit
        
        Sub head_and_footer()
        Dim wks As Worksheet, wks_def As Worksheet
        Set wks_def = Sheets("YourWorksheet")
        For Each wks In ThisWorkbook.Sheets
            With wks.PageSetup
                .LeftHeader = wks_def.PageSetup.LeftHeader
                .CenterHeader = wks_def.PageSetup.CenterHeader
                .RightHeader = wks_def.PageSetup.RightHeader
                .LeftFooter = wks_def.PageSetup.LeftFooter
                .CenterFooter = wks_def.PageSetup.CenterFooter
                .RightFooter = wks_def.PageSetup.RightFooter
            End With
        Next wks
        End Sub

         

         

         

        Thank you for your patience and time.

         

        Nikolino

        I know I don't know anything (Socrates)

        * Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

Resources