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

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.

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JCIrish" wrote in message ...
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.