Sleeper: Calculate number of pages per office

[Glaswegian]

I have a workbook where I'll be printing a large number of sheets. I've programatically set the page breaks, page size and all the headers etc required. The data is sorted by an ID code representing each office. Volumes are variable - some office will have only 5 lines of data, some 400 lines and a variety in between. A blank row separates each line of data. I can work out how many lines each of data each office will have. Is there a way to work out how many pages each office will receive when I print the pages? I had thought of using the HPageBreaks.Count property, but I'm not sure how to link that in with the ID code. How do I work out the number of data lines for each office that exist between each page break?

Thanks

[CBrine]

Couple of questions:
1. Are the page breaks keyed off the blank lines?
2. Where is the dept ID stored, the first coloumn?
3. You mentioned page counts first, then you mentioned data line counts? Which are looking for?
4. Once you have this information, where do you want to put it?


Cal

[Glaswegian]

Hi Cal

The Page Breaks are above the blank line where the dept ID changes (breaks within the same dept ID seem to be random)

Dept ID is in Col A

The info can be put in any blank sheet

I would like the to know the number of pages that will be printed for each dept ID - I worked out some code to give me the number of lines of data for each dept.

Thanks, as always, for your help.

[CBrine]

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