Take the 2-minute tour ×

Every google hit for applying the same header says to highlight all sheets and set a header. How can I ensure that new sheets I add also get the same header, without alway

share|improve this question
add comment

2 Answers

As a first attempt i would use this event:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   sh.range("A1").value = "xxx"
End Sub

you would have to add this macro to your Workbook, not to a Worksheet, not as a Module or Class.

Anotherway might be the use of an add-in, designed for this - this would be for the purpose of having this behaviour in every workbook.

share|improve this answer
add comment

Here is VBA code to copy the header and footer from the first sheet to new sheet. Insert this into ThisWorkbook in VBA Explorer (Alt+F11) and save it. It will run when you add a new sheet.

Adjust the 4th line, where it reads Sheet1, to be the name of your first sheet.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   Dim Ws As Worksheet
   'Change "Sheet1" below to the name of sheet being copied
   Set Setup = Worksheets("Sheet1").PageSetup
    With ActiveSheet.PageSetup
     .LeftHeader = Setup.LeftHeader
     .CenterHeader = Setup.CenterHeader
     .RightHeader = Setup.RightHeader
     .LeftFooter = Setup.LeftFooter
     .CenterFooter = Setup.CenterFooter
     .RightFooter = Setup.RightFooter
     End With
End Sub

If you want to run it manually, change the first line to

Sub CopyHeaderFooter()

It will then show up as a runnable macro (Alt+F8), named CopyHeaderFooter you can execute when you want.

share|improve this answer
I would rather not copy it to all sheets, just the new one, in case I customise the header on one sheet. –  ProfK Sep 21 '12 at 15:19
Sorry. My mistake. At first, I was going to copy to all, but changed it. This code only copies to the new sheet, not all sheets. –  CharlieRB Sep 21 '12 at 15:44
add comment

Your Answer


