VBA code to automate "page breaks"

[KWL]

I am seeking help developing VBA code to automate “page breaks” as follows:

I prepare a daily spreadsheet (2007 Excel) that varies in length (up to 5000 rows)…. Column A contains defined “groups” of various lengths beginning in A2 with a blank row separator between each group…. Each group varies in length anywhere from 4 to 20 rows. Is it possible to write VBA code that would automatically provide “page breaks” ensuring each individual group is never split into two separate pages?

Thanks in advance for any available help,

KWL

 

[ukmikeb]

Hi

Try this as a Workbook module (not tested) -

Private Sub Workbook_BeforePrint()
' 
Dim SelRange As Range
Dim HPg, InsertRowPos, PgCt, Pdiv
'
HPg = ActiveSheet.HPageBreaks.Count
Set SelRange = ActiveSheet.Range(ActiveSheet.PageSetup.PrintArea)
Application.ScreenUpdating = False
ActiveSheet.ResetAllPageBreaks
'
PgCt = 1
Do
    If HPg = 0 Then
        Exit Do
    End If
 
    Pdiv = ActiveSheet.HPageBreaks(PgCt).Location.Row
 
    With Range("D" & Pdiv)
        If .Value = .Offset(-1, 0).Value Then
        'Cell is not empty therefore find the first occurrence of this Group
            SelRange.AutoFilter Field:=1, Criteria1:=Range("D" & Pdiv).Value ' This Group
            InsertRowPos = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Row
            SelRange.AutoFilter Field:=1
            Set ActiveSheet.HPageBreaks(PgCt).Location = Range("D" & InsertRowPos)
        End If
    End With
 
    PgCt = PgCt + 1
Loop Until PgCt > ActiveSheet.HPageBreaks.Count
SelRange.AutoFilter
Application.ScreenUpdating = True
 
'
End Sub

change references to column D to the Location of your Grouping.

hth

 

[KWL]

Hi Mike,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thank you so much for your reply…. I really appreciate you taking the time to write this “code”!
<o></o>
<o> </o>
Unfortunately, I cannot get the code to work and as a VBA rookie, my attempts to edit have been unsuccessful.
<o></o>
<o> </o>
There are a couple things I did not make clear in my original post…. First, all my “groups” appear in column A beginning in A2 (A1 contains a column heading). There is also a blank row separator between each group found in Column A. (Note: This blank separator row does contain data in Column B only representing totals exclusive to each individual group…. All other columns corresponding to each unique (Column A) separator row are also “blank”.)
<o></o>
<o> </o>
My spreadsheet has multiple columns and also many hidden columns containing proprietary data. In turn, I am publishing a summary of specific data with the intent of viewing all corresponding columns on each individual page. For this reason, I have set the page scale to 75% to be printed in landscape view. This is reversed as per your code “ResetAllPageBreaks” as the scale defaults back to 100%. I cannot figure out how to fix?
<o></o>
<o> </o>
Any additional help would be greatly appreciated and thanks in advance for all your time and trouble.
<o> </o>
<o></o>
Best regards,
<o> </o>
KWL

 

[ukmikeb]

Hi

You won't see the module in the Macros list because it is "Private".

If you enter the VB Editor there is a window on the left for the which contains VBAProject the Workbook and associated worksheets. By scrolling down you should arrive at the module attached to the Workbook but it will have the name Modulen where n is a number 1 to....

You will be able to edit and save that relevant Module.

Try commenting out the ResetAllPageBreaks line.

The report/s addressed by a routine of which this is a sub-set handles groups which are already spaced out by blank lines but the Group-identifier (held in Column D) can be repeated anything from 3 to 40 times.

Is that a similar structure to your Groups in Column A? If so, you only need to change references to Column D in the module to Column A.

hth