# of pages and page#

[Junichi Kuroda]

Hi,

How can I find out the total number of pages on a worksheet?

Also, is it possible to know the page# that a particular area is on? I'd
like to know like "A80" will be printed on page4, that sort of things given
that I want to print the first few rows of the sheet on every page as a
header.

Thank you for your help.

Jun

[Theron Melcher]

Have you looked at the Menu Bar / View / Page Break Preview??
This shows The Pages , Page Numbers and (row,col) At least in Excel97 not
shure of any before that

Junichi Kuroda wrote in message <#6Owi07w#GA.289@cppssbbsa03>...

[Dana DeLouis]

Here is something I wrote a long time ago. Not tested for Excel 2k. Maybe
this will give you some ideas. I have numbered the pages left to right,
then down. Adjust it you want it different. Good luck. Dana

Sub Print_Page_of_ActiveCell()
'// Prints the page of the active cell only
'// Dana DeLouis: da...@msn.com
'// 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:=MyPage
End Sub

Junichi Kuroda <tats...@muc.biglobe.ne.jp> wrote in message
news:#6Owi07w#GA.289@cppssbbsa03...

[Dana DeLouis]

Sorry. I do not know how that last line got changed. Should be:

'// Print the selected page

'ActiveSheet.PrintOut From:=CurrentPage, To:=CurrentPage

Instead of:

> 'ActiveSheet.PrintOut From:=CurrentPage, To:=MyPage

Dana