Adding Different Headers to Each Worksheet in an Excel 2010 Workbook

Hello Experts,

I'm trying to print different headers on each worksheet in an Excel 2010 workbook that contains four worksheets. I'm not familiar with VBA code, but I found the code below online and modified it a bit in an attempt to center the headers on each worksheet.

Sub DifferentHeaderFooter()
    Dim ws As Worksheet
    Dim vLeft As Variant, vRight As Variant, xRg As Variant
    Set ws = ActiveSheet
    On Error Resume Next
    vLeft = Array("First page", "Second page", "Third page", "fourth page")
    xRg = Array("A1:C50", "A51:C100", "A101:C150", "A151:C200")
 
    Application.ScreenUpdating = False
    For i = 0 To UBound(vLeft)
        With ws.PageSetup
        .PrintArea = xRg(i)
        .LeftHeader = vLeft(i)
        End With
        ws.PrintPreview
    Next i
    Application.ScreenUpdating = True
    ws.PageSetup.PrintArea = ""
End Sub

I modified the code above as follows:

Sub DifferentHeaderFooter()
    Dim ws As Worksheet
    Dim vCenter As Variant, xRg As Variant
    Set ws = ActiveSheet
    On Error Resume Next
    vCenter = Array("Personal Docket Assignments" & Chr(13) & "1st Quarter - 2018" & Chr(13) & "January 2, 2018 - March 31, 2018", "Personal Docket Assignments" & Chr(13) & "2nd Quarter - 2018" & Chr(13) & "April 3, 2018 - June 30, 2018",
"Personal Docket Assignments" & Chr(13) & "3rd Quarter - 2018" & Chr(13) & "July 3, 2018 - September 29, 2018",
"Personal Docket Assignments" & Chr(13) & "4th Quarter - 2018" & Chr(13) & "October 2, 2018 - December 29, 2018")
     
    Application.ScreenUpdating = False
    For i = 0 To UBound(vCenter)
        With ws.PageSetup
        .PrintArea = xRg(i)
        .CenterHeader = vCenter(i)
        End With
        ws.PrintPreview
    Next i
    Application.ScreenUpdating = True
    ws.PageSetup.PrintArea = ""
End Sub

I removed the xRg = Array("A1:C50", "A51:C100", "A101:C150", "A151:C200") code because I set the print area using the Print Area option on the Page Layout tab for each worksheet. The result is the print area is correct for all four worksheets, but the header that I have designated for the fourth worksheet, prints out centered correctly on the first and fourth worksheets only. There are no headers printing out on the second and third worksheets.
Janice SmithSystems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
That code is only going to set the header for the active sheet, how are you running it?

Also, where is the code located?
0
Rgonzo1971Commented:
Hi,

pls try
Sub DifferentHeaderFooter()
     Dim ws As Worksheet
     Dim vCenter As Variant, xRg As Variant
     Set ws = ActiveSheet
     On Error Resume Next
     vCenter = Array("Personal Docket Assignments" & Chr(13) & "1st Quarter - 2018" & Chr(13) & "January 2, 2018 - March 31, 2018", "Personal Docket Assignments" & Chr(13) & "2nd Quarter - 2018" & Chr(13) & "April 3, 2018 - June 30, 2018", 
 "Personal Docket Assignments" & Chr(13) & "3rd Quarter - 2018" & Chr(13) & "July 3, 2018 - September 29, 2018", 
 "Personal Docket Assignments" & Chr(13) & "4th Quarter - 2018" & Chr(13) & "October 2, 2018 - December 29, 2018")
      
     Application.ScreenUpdating = False
     For i = 0 To UBound(vCenter)
         With Sheets(i).PageSetup
         .PrintArea = xRg(i)
         .CenterHeader = vCenter(i)
         End With
         ws.PrintPreview
     Next i
     Application.ScreenUpdating = True
     ws.PageSetup.PrintArea = ""
 End Sub

Open in new window

Regards
0
Janice SmithSystems AnalystAuthor Commented:
I opened the VBA window by clicking the Developer tab and then clicking Visual Basic. I pasted the code into that window and then hit F5.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Janice SmithSystems AnalystAuthor Commented:
@Rgonzo1971 , now the 1st header doesn't print. The 2nd header prints out on the 1st page and the 3rd header on the 2nd worksheet. The last header prints out on the 3rd an 4th worksheets.
0
Janice SmithSystems AnalystAuthor Commented:
My apologies. I think I may have made this far more complicated than necessary. I just added different headers for each worksheet on the Header/Footer tab under Page Setup.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Janice SmithSystems AnalystAuthor Commented:
It was easier to create custom headers under the Page Set up option in Excel.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.