Page Breaks when value changes

Hi all

I want some code which will automatically insert a page break every time the data changes in a column. In column B- the data is structured as below;

DOG
<blank cell>
<blank cell>
DOG
<blank cell>
DOG
<blank cell>
<blank cell>
CAT
<blank cell>
CAT
<blank cell>
MOUSE
<blank cell>
MOUSE
MOUSE
<blank cell>

Etc

The code I want would insert the page break every time every time the data changed- but not where there is a blank cell. I hope this makes sense. I realise the blank cells complicate things- the blank cells separating the data cells are there because there is additional;data in columns C,D,E etc

Many thanks for any help anyone can give me with this problem
J

redjay,
use the following line to assign the pagebreak were you need it.

   dim BreakPosition as long  'This is the Row number where you want the break to appear.


   BreakPosition=25  'Modify this line to suit your needs/

   'This is for a Horizontal PageBreak.
   Worksheets("Sheet1").HPageBreaks.Add Before:=Rows(BreakPosition)

   'Rename "Sheet1" with the name of the Sheet where you want the Pagebreak to appear.

Not quite sure I understand what do you need.
How do you know were to place the Pagebreaks esactly?

Hi wizz

I need the pagebreaks to be inserted automatically wherever there is a change in the data in column B. So in the example, wherever the data changed from DOG to CAT, thats where I need the page break to go....

J

So, in the following case:
________________________________
DOG
<blank cell>
DOG
<blank cell>
<blank cell>
CAT
<blank cell>
CAT
________________________________

You would need the pagebreak under the last blank cell over CAT or it should be under the las DOG cell (before the blank cell)?

Under the last blank cell before CAT would be perfect....

Then this should do the trick

Sub SetHPageBreaks()
    
    Dim LastDataRow As Long
    Dim lngRow As Long              'Counter to loop through each row
    Dim EvaluatingData As String
    Dim SheetName As String
    
    SheetName = ThisWorkbook.Worksheets(1).Name                             'Sheet name where the data is located.
    LastDataRow = Worksheets(SheetName).Cells(Rows.Count, 2).End(xlUp).Row  'This is the row number of the last cell with data in Column B
    EvaluatingData = Worksheets(SheetName).Cells(LastDataRow, 2).Value      'Value inside each cell being evaluated during the loop.
    
    Worksheets(SheetName).ResetAllPageBreaks  'This line will errase all the custom Pagebreaks.  if you have custom pagebreaks that you need to keep, then delete.
    
    For lngRow = 1 To LastDataRow  'Modify the 1 with the row number for the first data value.
        
        If Worksheets(SheetName).Cells(lngRow, 2).Value <> EvaluatingData And Worksheets(SheetName).Cells(lngRow, 2).Value <> "" Then
            Worksheets(SheetName).HPageBreaks.Add Before:=Rows(Worksheets(SheetName).Cells(lngRow, 2).Row)
            EvaluatingData = Worksheets(SheetName).Cells(lngRow, 2).Value
            
        End If
    
    Next

End Sub

Hi Wizz

Im a bit of a newb so might need some more help here.

when I try to run SetHPageBreaks, I get a 'Subscript out of range' error?

Thanks for your help so far

J

Hi redJay;
Wich line is highlighted when the error pops?

Wizz,

Its ok, have solved that problem

One further thing though, can I amend the code so that instead of inserting the page break directly above the change in data- it instead inserts it on the row before that (i.e one row prior to the change in data) ?

Thanks!

for sure; if you dont care if the previous row is blank or nonBlank, then use the following:

Worksheets(SheetName).HPageBreaks.Add Before:=Rows(Worksheets(SheetName).Cells(lngRow-1, 2).Row)

Otherwise, we'll need some more modification. Just let me know.