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:
Code
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
Display More
What the code is doing: is it works for the 1st page, but nothing after that.