Forum Discussion

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?

Macros and VBA

office 365

NikolinoDE

Platinum Contributor

Jul 08, 2021

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

Copper Contributor

Jul 08, 2021

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

Platinum Contributor

Jul 08, 2021

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.