Macro to set page breaks

[howard]

I have the following code below to set page breaks for a range called "Journals"

The page breaks in the range Journals are to show the breaks as follows to be Row 12:66, 67:118, 119:162, 163:180

 Sub SetPageBreaksForJournals()

    Dim rng As Range
    Dim i As Long
    
    'Set the range "Journals"
    Set rng = Range("Journals")
    
    'Clear existing page breaks in the range
    rng.ResetAllPageBreaks
    
    'Set new page breaks
    rng.Rows(1).PageBreak = xlPageBreakManual 'Start the first page at row 1
    
    'Loop through each set of rows and set page breaks
    For i = 12 To 163 Step 55 'Increment by 55 to cover the desired range
        rng.Rows(i).PageBreak = xlPageBreakManual
    Next i
    
    'Set the last page break to the end of the range
    rng.Rows(181).PageBreak = xlPageBreakManual 'End the last page at row 181
    
End Sub

However when running the code, I get a run time error "Object does'nt support this property or object" and the code below is highlighted

 rng.ResetAllPageBreaks

It would be appreciated if someone could amend my code

 

[Jimmypop]

hi there

Try the code below:

Sub SetPageBreaksForJournals()

Dim rng As Range
Dim i As Long

'Set the range "Journals"
Set rng = Range("Journals")

'Clear existing page breaks in the range
rng.Worksheet.ResetAllPageBreaks

'Set new page breaks
rng.Rows(1).PageBreak = xlPageBreakManual 'Start the first page at row 1

'Loop through each set of rows and set page breaks
For i = 12 To 163 Step 55 'Increment by 55 to cover the desired range
    rng.Rows(i).PageBreak = xlPageBreakManual
Next i

'Set the last page break to the end of the range
rng.Rows(181).PageBreak = xlPageBreakManual 'End the last page at row 181

end sub

 

[howard]

Thanks for the help.

When runing your code, I get a run time error "Unable to set the pagebreak property of the range class

the code below is highlighted

 rng.Rows(1).PageBreak = xlPageBreakManual 'Start the first page at row 1

Kindly amend

 

[Jimmypop]

Try the below... Tested and not sure how your page layout must look but code below works without error...

Sub SetPageBreaksForJournals()

Dim rng As Range
Dim i As Long

'Set the range "Journals"
Set rng = Range("Journals")

'Clear existing page breaks in the range
rng.Worksheet.ResetAllPageBreaks

'Set new page breaks
rng.Rows(2).PageBreak = xlPageBreakManual 'Start the first page at row 1

'Loop through each set of rows and set page breaks
For i = 12 To 163 Step 55 'Increment by 55 to cover the desired range
    rng.Rows(i).PageBreak = xlPageBreakManual
Next i

'Set the last page break to the end of the range
rng.Rows(181).PageBreak = xlPageBreakManual 'End the last page at row 181

End Sub

 

[howard]

Thaanks for the help