Janice Smith
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.
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
For i = 0 To UBound(vLeft)
With ws.PageSetup
.PrintArea = xRg(i)
.LeftHeader = vLeft(i)
End With
ws.PrintPreview
Next i
Application.ScreenUpdating
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
For i = 0 To UBound(vCenter)
With ws.PageSetup
.PrintArea = xRg(i)
.CenterHeader = vCenter(i)
End With
ws.PrintPreview
Next i
Application.ScreenUpdating
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.
Hi,
pls try
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
Regards
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.
ASKER
@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It was easier to create custom headers under the Page Set up option in Excel.
Also, where is the code located?