Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.