Whew,
That was much more difficult then I thought it would be. Here's what you need to do.
1. Setup a worksheet called "Summary"(Or change my code to a sheetname you want to use.)
2. Setup your code to call my commandButton1_click code, or set it up on your own button.
This will only count pages horizontally, so if you have vertical pagebreaks as well, the code will need to be modified.
The worksheet must have a row space between each of the data sets. It will then take the first entry in column A of the section and use that as the summary row name and place the pagebreak count beside it. You can add titles to the summary page.
HTH
Cal
Private Sub CommandButton1_Click()
Dim Count As Integer, StartRange As Range, EndRange As Range, ws As Worksheet, Pages As String, PrintRange As Range
Application.DisplayAlerts = False
Set StartRange = Range("A1")
If StartRange.Offset(1, 0) = 0 Then
Set EndRange = StartRange
Else
Set EndRange = Range("A1").End(xlDown)
End If
ActiveSheet.PageSetup.PrintArea = StartRange.Address & ":" & EndRange.Address
Do
Sheets("summary").Range("A65536").End(xlUp).Offset(1, 0).Value = StartRange
Sheets("Summary").Range("A65536").End(xlUp).Offset(0, 1).Value = CountOfPages(StartRange.Offset(1, 0).Row, EndRange.Row)
Set StartRange = EndRange.Offset(2, 0)
If StartRange.Offset(1, 0) = "" Then
Set EndRange = StartRange
Else
Set EndRange = StartRange.End(xlDown)
End If
ActiveSheet.PageSetup.PrintArea = StartRange.Address & ":" & EndRange.Address
If EndRange = "" Then
Set PrintRange = Range("A1", StartRange.Offset(-2, 0))
ActiveSheet.PageSetup.PrintArea = PrintRange.Address
Application.DisplayAlerts = True
Exit Sub
End If
Loop
End Sub
Function CountOfPages(StartRangeRow As Variant, EndRangeRow As Variant)
Dim iHpBreaks As Integer, PrintRange As Range, cell As Range
Set PrintRange = Range("A" & StartRangeRow & ":A" & EndRangeRow)
iHpBreaks = 1
For Each cell In PrintRange
If cell.EntireRow.PageBreak = xlPageBreakManual Or cell.EntireRow.PageBreak = xlPageBreakAutomatic Then iHpBreaks = iHpBreaks + 1
Next
CountOfPages = iHpBreaks
End Function