The attached file is an example of a monthly report I produce.
- Please Select Page Break View and notice the Group Name Column and notice where the page break falls.
- I want the code to keep the client names together. (The name of the client falls under the Group Name). So for example on page 1 you would have all of Ale House, Acfa, AHCI, CHS, BENE H, and BCare. The page break should fall under the Total in Col E.
- page 2 you would have, Bfuel, C Inst, CFDIS, CM and CO,
and so on.
What I have so far:
Sub PrintFormat() Application.ScreenUpdating = False Dim i As Long, j As Long, k As Long, StrRows As String With ActiveSheet .UsedRange .ResetAllPageBreaks 'Scale to 1 page wide. .PageSetup.FitToPagesWide = 1 .PageSetup.FitToPagesTall = False 'Insert manual page breaks at every blank line. For i = 5 To .Cells.SpecialCells(xlCellTypeLastCell).Row If Trim(Cells(i, 10).Value) = "" Then .Cells(i, 1).PageBreak = xlPageBreakManual StrRows = StrRows & "," & i End If Next 'Progressively delete manual page breaks and check whether the page count decreases. ' If not, we've split a record set, so reinstate the manual page break. For i = 1 To UBound(Split(StrRows, ",")) j = ExecuteExcel4Macro("GET.DOCUMENT(50)") k = Split(StrRows, ",")(i) .Cells(k, 1).PageBreak = xlNone If ExecuteExcel4Macro("GET.DOCUMENT(50)") = j Then .Cells(k, 1).PageBreak = xlPageBreakManual End If Next End With Application.ScreenUpdating = True End Sub
What the code is doing: is it works for the 1st page, but nothing after that.