How to add "Page number and Pages number" in cell ?

[Simon Chui]

How to add "Page number and Pages number" in cell ? ( not use Header & Footer).

 

[Frank Kabel]

Hi
for getting the last page number into a cell you may use
the following user defined function:
public function last_page()
last_page = Application.ExecuteExcel4Macro("GET.DOCUMENT
(50)")
end function

Getting the current page is complicated as you have to
calculate the current printer settings, etc. So the
easiest way: use header/footer for this

 

[Paul B]

Simon, here is a macro that will do it
Sub pagenumber()
'will not update automatically
'will put page of page in the active cell
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
ActiveCell = "Page " & NumPage & " of " & _
Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

How to add "Page number and Pages number" in cell ? ( not use Header &

Footer).