Retaining original table formatting after a 'pagebreak'

Question

So here's the finished product, a statement of accounts with a working statement table, and an ageing analysis:

Everything works great. It basically populates itself row by row with data from another table. Here is the sample code:

j = 21 'First row on the statement of accounts workbook

For k = 1 To TSOA.ListRows.Count 'TSOA is the original data table

If Not TSOA.DataBodyRange.Rows(k).Hidden Then 'excludes the filtered entries

    SOAwb.Worksheets(1).Cells(j, 4) = TSOA.DataBodyRange(k, 6) 'Debit
    SOAwb.Worksheets(1).Cells(j, 5) = TSOA.DataBodyRange(k, 7) 'Credit
    SOAwb.Worksheets(1).Cells(j, 1) = TSOA.DataBodyRange(k, 3) 'Date

    <some other similar code goes here>

    j = j + 1 'forces next row

    If (j + 4) Mod 50 = 0 Then 'Increase footer, since there are only 50 rows in a page
        j = j + 12 'Increase header
    End If
End If

Next

So I coded in a 'somewhat' dynamic pagebreak, using the line of code:

If (j + 4) Mod 50 = 0 Then
j = j + 12 'Increase header
End If

where (j + 4) is the trigger for the footer pagebreak, Mod 50 divides (j+4) by 50 and gives you the remainder. Hence if its perfectly divisible, the result = 0. j + 12 helps to skip past the header logos, you'll understand why in the next picture.

So the line of code basically works if you didn't care about the subsequent table formatting: ><

So does anyone know how do I continue with the previous table's formatting, ie the green and white statement table in the original page in the second page? Or is there some way to preload the table formatting in the second page (bearing in mind that not all statements need a second page). Or perhaps even tinkering about the print settings when the pagebreak triggers? Or any other creative solutions?

I have had zero experience with dealing with multiple pages using VBA, and quite frankly, I do not even know how to go about navigating between pages. I can't stress hard enough that this code is my amateur attempt to do useful things with excel, so there must be room for improvement!

Answer 1

Just change the page margins and the top few rows get excluded, but the manual page break is still required though!