coditional Page Break

  • Thread starter Thread starter j4m3sc
  • Start date Start date
J

j4m3sc

Hi

I have a problem where i need to use automated page breaks on excel.

I'd like a page break each time the word "accounting" appears in Column A.
To make my problem even harder for me is that there are blank cells in
Column A.

I know VGA code will be needed but cannot do this myself.

Please help
 
Sub Insert_PBreak_Col()
Dim rng As Range
Dim Cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Columns(1))
For Each Cell In rng
If Cell.Text = "accounting" Then
Cell.Offset(1, 1).PageBreak = xlPageBreakManual
End If
Next
End Sub

Will insert a pagebreak below the word "accounting"

If you want it above, change the line

Cell.Offset(1, 1).PageBreak = xlPageBreakManual to

Cell.PageBreak = xlPageBreakManual


Gord Dibben MS Excel MVP
 
Hi,

A non macro approach would be using the Data > Subtotal feature. First of
all sort column A. Then highlight the range and navigate to Data >
Subtotal. Provide the relevant inputs and check the box for "Create page
breaks"

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top