Horizontal page break location report - an example

[DataFreakFromUtah]

No question here, just a procedure for the archive.

Search terms: horizontal page break report, return page break rows,
HPageBreaks report, pagebreaks report, page break location, manual and
automatic pagebreaks, summary of pagebreaks, row numbers


Create a new report worksheet that contains the row of numbers
of all manual & automatic horizontal page breaks on the
active worksheet


Sub PageBreakHorizontalReport()

'Creates a new report worksheet that contains the row of numbers
'of all manual & automatic horizontal pagebreaks on the
'active worksheet

Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim Row As Integer


On Error Resume Next

'Add a new worksheet
Application.ScreenUpdating = False
Set TargetWorksheet = ActiveWorkbook.ActiveSheet
Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name

'Set up the column headings for Report worksheet
With PageBreakSheet
Range("A1") = "PageBreak Row"

'Optional 2nd reference that can be used to return
'a cells value that has the horizontal page break
Range("B1") = "PageBreak Cell Value"

Range("A1:B1").Font.Bold = True
End With

'Process each pagebreak
Row = 2
For Each hb In TargetWorksheet.HPageBreaks

With PageBreakSheet
Cells(Row, 1).Value = hb.Location.Row

'Optional 2nd reference that can be used to return
'a cell's value that has the horizontal page break on
'it's row. 'In this case it takes the value in column A with
'the row number of the page break. Adjust
Cells(hb.Location.Row, #) as
'needed.

Cells(Row, 2).Value =
CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)
Row = Row + 1
End With
Next

'Adjust column widths on Report sheet
PageBreakSheet.Columns("A:B").AutoFit
Application.StatusBar = False

'Select a cell on the top of the report worksheet
Range("A2").Select

End Sub

 

[DataFreakFromUtah]

Oops! I forgot to add one line of code in the dim section for the procedure
The line should be:

Dim hb as HPageBreak

The full, corrected procedure is below:

Sub PageBreakHorizontalReport()

'Creates a new report worksheet that contains the row of numbers
'of all manual & automatic horizontal pagebreaks on the
'active worksheet

Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim Row As Integer
Dim hb as HPageBreak

On Error Resume Next

'Add a new worksheet
Application.ScreenUpdating = False
Set TargetWorksheet = ActiveWorkbook.ActiveSheet
Set PageBreakSheet = ActiveWorkbook.Worksheets.Add
PageBreakSheet.Name = "Pagebreaks in " & TargetWorksheet.Name

'Set up the column headings for Report worksheet
With PageBreakSheet
Range("A1") = "PageBreak Row"

'Optional 2nd reference that can be used to return
'a cells value that has the horizontal page break
Range("B1") = "PageBreak Cell Value"

Range("A1:B1").Font.Bold = True
End With

'Process each pagebreak
Row = 2
For Each hb In TargetWorksheet.HPageBreaks

With PageBreakSheet
Cells(Row, 1).Value = hb.Location.Row

'Optional 2nd reference that can be used to return
'a cell's value that has the horizontal page break on
'it's row. 'In this case it takes the value in column A with
'the row number of the page break. Adjust
Cells(hb.Location.Row, #) as
'needed.

Cells(Row, 2).Value =
CStr(TargetWorksheet.Cells(hb.Location.Row, 1).Value)
Row = Row + 1
End With
Next

'Adjust column widths on Report sheet
PageBreakSheet.Columns("A:B").AutoFit
Application.StatusBar = False

'Select a cell on the top of the report worksheet
Range("A2").Select

End Sub