ExcelBanter - page break location in VBA

this code written by Dana de Louise doesn't do what you want, but provides
the ingrediants you need to do it. You would need to find the rows with
pagebreaks and then determine which page goes with each (by keeping track).

Sub Print_Page_of_ActiveCell()
'// Prints the page of the active cell only
'// Dana DeLouis:
'// Not tested completely for Excel 2k

Dim pb As Variant
Dim Nr As Long, Nc As Integer
Dim MaxColumns As Long
Dim MaxRows As Long
Dim CurrentPage As Long
Dim TotalPages As Long

If Selection.Cells.Count 1 Then
MsgBox "Select 1 Cell Only"
Exit Sub
End If

'// Save Settings
ActiveWorkbook.CustomViews.Add _
ViewName:="_temp", _
PrintSettings:=True, _
RowColSettings:=True

With ActiveSheet.PageSetup
.PrintArea = False
.Order = xlOverThenDown
End With

TotalPages = ExecuteExcel4Macro("Get.Document(50)")
MaxColumns = ActiveSheet.VPageBreaks.Count + 1
MaxRows = ActiveSheet.HPageBreaks.Count + 1
' or MaxRows = TotalPages / MaxColumns

For Each pb In ActiveSheet.HPageBreaks
If pb.Location.Row <= ActiveCell.Row Then
Nr = Nr + 1
Else
Exit For
End If
Next

For Each pb In ActiveSheet.VPageBreaks
If pb.Location.Column <= ActiveCell.Column Then
Nc = Nc + 1
Else
Exit For
End If
Next

Nc = Nc + 1
CurrentPage = Nr * MaxColumns + Nc

'// Reset what I had
With ActiveWorkbook.CustomViews("_temp")
.Show
.Delete
End With

MsgBox "Page : " & CurrentPage & " out of " & TotalPages

'// Print the selected page
'ActiveSheet.PrintOut From:=CurrentPage, To:=CurrentPage
End Sub

--
Regards,
Tom Ogilvy

"mcphc" wrote:

How do I find the row number of where a horizontal page break is located for
a given page number on a worksheet.