Link to home
Start Free TrialLog in
Avatar of Janice Smith
Janice SmithFlag for United States of America

asked on

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.
Avatar of Norie
Norie

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

Also, where is the code located?
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
Avatar of Janice Smith

ASKER

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.
@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.
ASKER CERTIFIED SOLUTION
Avatar of Janice Smith
Janice Smith
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It was easier to create custom headers under the Page Set up option in Excel.