My table has the categories all in column A, starting with "A3". The following columns have the data and the number of columns may vary each time the report is run. Row 1 has the chart title and row 2 has a legend ("A1:G2"). "H1" to end is blank. Since the data is dynamic the number of pages also varies. I'd like to have the title & legend on the top of each page.
If I list the rows in page setup, the entire row is selected. The information I need repeated is only in ("A1:G2"). I can't code to copy and paste "A1:G2" because I never know how many pages I'll have. The workbook title is listed as the header on all pages.
Public Sub testsub()
Dim ws As Worksheet
Dim surf As Worksheet
With surf.PageSetup
.PrintTitleRows = "$1:$2"
.PrintTitleColumns = "$A:$A"
End With
Application.PrintCommunication = True
surf.PageSetup.PrintArea = ""
With surf.PageSetup
.LeftHeader = ""
.CenterHeader = "Test Workbook"
.RightHeader = ""
.LeftFooter = "&D"
.CenterFooter = "&G"
.RightFooter = "&P"
.CenterHorizontally = True
.CenterVertically = True
End With
Application.PrintCommunication = True
End Sub
I'd like "A1:G2" on each page of the worksheet. Thank you!
This solution uses the Workbook Event Workbook_BeforePrint
Copy the following procedures in the ThisWorkbook
object module of your workbook:
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call Print_Header_Update
End Sub
Sub Print_Header_Update()
Dim ws As Worksheet, vpb As VPageBreak, rHdr As Range, rg As Range
Set ws = ThisWorkbook.Worksheets("DATA") 'Update as required
With ws
Set rHdr = .Range("B1:G2") 'Update as required
Set rg = rHdr.Columns(8).Resize(2, -8 + .Columns.Count) 'Update as required
rg.ClearContents
For Each vpb In ws.VPageBreaks
rHdr.Copy
vpb.Location.Cells(1).PasteSpecial
Application.CutCopyMode = False
Selection.EntireColumn.AutoFit 'This might require fine-tuning
Next: End With
End Sub
for detailed information see:
Workbook.BeforePrint event (Excel),
Worksheet.VPageBreaks property (Excel),
Range.Resize property (Excel),
Range.AutoFit method (Excel)