VBA Code to Insert PAge Breaks

jhabey · May 12th 2016

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.

jaslake · May 13th 2016

Re: VBA Code to Insert PAge Breaks

Hi jhabey

I must be missing something as your Code appears to work as expected. I did add a couple lines of Code to set the Top and Bottom Margins to get this distribution...

Quote

page 1 you would have all of Ale House, Acfa, AHCI, CHS, BENE H, and BCare
page 2 you would have, Bfuel, C Inst, CFDIS, CM and CO

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.TopMargin = Application.InchesToPoints(0.5)   '<---Added
      .PageSetup.BottomMargin = Application.InchesToPoints(0.5)   '<---Added
      .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

Edit: What version of Excel are you using?

jhabey · May 13th 2016

Re: VBA Code to Insert PAge Breaks

Hmmm, that is interesting. I just tested it with the code you provided and sure enough it is working to the way I intended. I wanted to get this code down as it looks more professional when it did not cut off in the middle of the Clients Data. Anyway thanks for providing feedback

jaslake · May 13th 2016

Re: VBA Code to Insert PAge Breaks

Hi jhabey

Glad you got it sorted.

You posted an xls File...what version of Excel are you using? Code could perhaps be enhanced if Excel 2010 or later.

jhabey · May 13th 2016

Re: VBA Code to Insert PAge Breaks

I am using 2010. Enhanced? What ideas did you have?

jaslake · May 14th 2016

Re: VBA Code to Insert PAge Breaks

Google on this...works in Excel 2010 and later...will reduce processing time dramatically...

Code

Application.PrintCommunication = False

VBA Code to Insert PAge Breaks

Participate now!

Share