D
DataFreakFromUtah
No question here, just a procedure for the archive.
THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
(FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT
Sub PageBreaksHorizontalReportALL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL & AUTOMATIC 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
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
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)
Cells(Row, 3).Value = NumPage
Row = Row + 1
End With
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
THIS PROCEDURE INCLUDES/RETURNS THE PRINT PAGE NUMBER
(FOR EACH MANUAL & AUTOMATIC PAGEBREAK) IN THE REPORT
Sub PageBreaksHorizontalReportALL()
'Creates a new report worksheet that contains the row of numbers
'of all MANUAL & AUTOMATIC 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
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
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)
Cells(Row, 3).Value = NumPage
Row = Row + 1
End With
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