Excel 2002 VBA code to return a page number within a worksheet

[Guest]

How do I write the code to return a page number within a given worksheet, so
that that number can be used (1) in an expression or (2) to cut, paste,
delete, etc the page? Also, the code to return the number of pages in a
worksheet? Thanks very, very much for any assistance.

 

[Ron de Bruin]

Hi JCIrish

You can do this but it is not fast

This will give you the pages
pgnum = ExecuteExcel4Macro("Get.Document(50)")

This will copy the pages to another sheet
http://www.rondebruin.nl/hpagebreaks.htm


This give you the activecell page

Sub CurrentPage()
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak, HPB As HPageBreak
Dim NumPage As Integer

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
For Each VPB In ActiveSheet.VPageBreaks
If VPB.Location.Column > ActiveCell.Column Then Exit For
NumPage = NumPage + HPC
Next VPB
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB

MsgBox "Page number of the active cell = " & NumPage

' ActiveWindow.SelectedSheets.PrintOut _
' From:=NumPage, To:=NumPage, _
' Copies:=1, Collate:=True

End Sub