Adding Page breaks every ninth row

[oam]

I have report that has varying amounts of information from 1 row up to 30 rows maximum. Two of my location needs the report broken down into nine rows per sheet until all the information is contained in the sheets. All the locations are selected using a dropdown menu (cell B1)
I need a macro to automatically add a page break every ninth row based on cell content until all the data is displayed on different pages.

Hope this is clear
Thank for all your help.

[SamT]

This should give you ideas

'Clear all PageBreaks
.Cells.PageBreak = xlPageBreakNone


If Range("B1") = "Location1" Or Range("B1") = "Location2" Then
For Rw = 9 To UsedRange.Rows.Count Step 9
Rows(Rw).PageBreak = xlPageBreakManual
Next
End If

[oam]

I am getting an "Object Required" error with the code provided, am I missing something?

Also, I forgot a detail, I need the first page to be 16 rows and the remaining rows to be nine, sorry!




Sub AutoPageBreak()
If Range("B1") = "SHG" Or Range("B1") = "SNY" Then
For Rw = 9 To UsedRange.Rows.Count Step 9
Rows(Rw).PageBreak = xlPageBreakManual
Next
End If
End Sub

[SamT]

You should Declare the Variable, it's good programming practice.

The Object required is probably the Worksheet, and both the ranges in the clear all PBs section and the add PBs section should reference it.

Hardcode the PB at Range "Row 16," (it'll need to refer to the Object, also,) and start the For loop at 25 (16 + 9). I would use a With block, myself.

If you have any specific questions, please feel free to ask. If you place the cursor in, or next to, any key word in the VBA editor and press F1, you will get Help on that word.

[oam]

Do you mean by declaring it "Dim Rw As Worksheet"? Could you give me an example of how you would do it if the sheet name was "Parts Summary"?

[SamT]

Option Explicit 'At the top of all code pages

Sub AutoPageBreak()
Dim Rwo As Long 'Row Index Variable
Dim RC As Long 'RC;= Rows Count. Holds the number of Rows in the UsedRange

With Sheets("Parts Summary")
RC = .UsedRange.Rows.Count 'Because UsedRange doesn't always start in Row 1

If .Range("B1") = "SHG" Or .Range("B1") = "SNY" Then
.Cells.PageBreak = xlPageBreakNone 'Clear all PageBreaks
.Rows(17).PageBreak = xlPageBreakManual 'Hardcoded PageBreak after row 16

For Rwo = 26 To .UsedRange.Rows(RC).Row Step 9
.Rows(Rwo).PageBreak = xlPageBreakManual
Next
End If
End With
End Sub

[oam]

Perfect!!

Thank you for all your help!