Manual Horizontal Pagebreak Report - an example

[DataFreakFromUtah]

No question here, just a procedure for the archive.

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


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

Sub PageBreaksHorizontalReportMANUAL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL horizontal pagebreaks on the
'active worksheet

Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim hb As HPageBreak

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
If hb.Type = xlPageBreakManual Then
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
End If
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]

No question here, just a procedure for the archive.

THIS PROCEDURE IS THE SAME AS THE PREVIOUS, BUT INCLUDES/RETURNS THE
PRINT PAGE NUMBER (FOR EACH MANUAL PAGEBREAK) IN THE REPORT

Sub PageBreaksHorizontalReportMANUAL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL horizontal pagebreaks on the
'active worksheet. INCLUDES PRINT PAGE NUMBER IN REPORT

Dim cell As Range
Dim PageBreakSheet As Worksheet
Dim TargetWorksheet As Worksheet
Dim hb As HPageBreak
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak
Dim NumPage As Integer



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

'Section sets up print page number counting specs:
If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1

'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("C1") = "Print Page Number"
Range("A1:C1").Font.Bold = True
End With

'Process each pagebreak
Row = 2
For Each hb In TargetWorksheet.HPageBreaks
'Derive Page Number of page break
NumPage = NumPage + VPC

If hb.Type = xlPageBreakManual Then
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)

'Reports the print page number of the Manual pagebreak
Cells(Row, 3).Value = NumPage

Row = Row + 1

End With
End If
Next

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

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

End Sub