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!