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: (e-mail address removed)
'// 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