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!

Have you tried using the [`Print Titles`](https://support.office.com/en-us/article/print-rows-with-column-headers-on-top-of-every-page-d3550133-f6a1-4c72-ad70-5309a2e8fe8c) manually? Without using VBA first? It should work once set up correctly. — L42, Aug 06 '19 at 01:43
I tried Print Titles, but I must not have it set up correctly. I entered $1:$2 as rows to repeat and $A:$A as columns to repeat. This only prints column A on the left. I've found information about how to print either rows or columns, but not both. Any suggestions? — RebaS, Aug 06 '19 at 02:27

score 1 · Accepted Answer · answered Aug 06 '19 at 02:25

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)

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

1 Answers1