Auto Page Breaks

[ExcelRoy]

Hi all,

I have a macro that saves to pdf and this works great, thanks to all your help

Is there a way to insert page breaks depending on data in column D

Column D has the headings for each selection of data beginning with "ZZ099...."

Hope this makes sense

Thanks

 

[jolivanes]

Sub Add_Page_Breaks()
Dim c As Range
ActiveSheet.ResetAllPageBreaks
    For Each c In Range("D1:D" & Cells(Rows.Count, 4).End(xlUp).Row)
        If Left(c.Value, 5) = "ZZ099" Then ActiveSheet.HPageBreaks.Add Before:=c
    Next c
End Sub

 

[ExcelRoy]

Hi jolivanes,

Many thanks for your help but this doesn't seem work

Each ZZ will have different numbers after it if that matters

Also I had set the scaling to 70% and the code resets this to 100%

Many thanks

 

[jolivanes]

If you change

If Left(c.Value, 5) = "ZZ099"

to

If Left(c.Value, 2) = "ZZ"

Do you think that that might work?
If not, there are other ways.
As for the scaling, i am sure you can reset it in the code. Lets us know. (I assume you mean the Zoom level)
BTW, when I tried the code with the Zoom level set at 70, nothing changed in the Zoom level. Have you got something else in a macro that could trigger the change?

 

[jolivanes]

Would this do it?

Sub Add_Page_Breaks()
Dim c As Range, zm As Long
zm = ActiveSheet.PageSetup.Zoom
ActiveSheet.ResetAllPageBreaks
    For Each c In Range("D1:D" & Cells(Rows.Count, 4).End(xlUp).Row)
        If Left(c.Value, 2) = "ZZ" Then ActiveSheet.HPageBreaks.Add Before:=c
    Next c
ActiveSheet.PageSetup.Zoom = zm
End Sub

 

[ExcelRoy]

Hi jolivanes,

Many thanks for the code, it almost works how I need it to

It does put every "ZZ" on a different page instead of as many "complete" ZZ's it can

Thanks

 

[jolivanes]

If you put page breaks at intervals, it's meant to do that. That is what you asked for.

 

[ExcelRoy]

Hi jolivanes,

my apologies, is there a way to allow as many "complete" ZZ's on one sheet, then pagebreak and again on the second etc etc

Thanks