How to have the table header on the top of each page if horizontally dynamic

Question

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!

Answer 1

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)