Can I Get PageNumber of Cell in Excel ?

Phone Ring ……… Phone Ring ……… The CID informed me that it was my good friend Timos on the other end .

Timos : Hey Nickos , Good morning can you tell me if there is an easy way to get the page number of a cell ??? I want to summarize a column based on page breaks

Me: Hmmmmm . Interesting problem . Let me get back to you in a while.

I must admit . that was a thing i could easily name , an interesting problem . Until that time the page number in excel for me was something that existed in the sphere of print settings and i had never involved it in and calculation inside the sheet .

That was surely an interesting problem !!!

Did some search and found some interesting stuff in forum posts .

The best one i found was mentioned in ( http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/8ca260935d83f46a )

Here is the solution transformed in VBA Function you can easily insert into Excel VBA Editor .

Public Function getpagenumber(CurrentCell As Range) As String

 Dim VPC As Integer, HPC As Integer

Dim VerticalPageBreak As VPageBreak, HorizontalPageBreak 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 VerticalPageBreak In ActiveSheet.VPageBreaks

  If VerticalPageBreak.Location.Column > CurrentCell.Cells.Column Then Exit For

  NumPage = NumPage + HPC

Next VerticalPageBreak

For Each HorizontalPageBreak In ActiveSheet.HPageBreaks

  If HorizontalPageBreak.Location.Row > CurrentCell.Cells.Row Then Exit For

  NumPage = NumPage + VPC

Next HorizontalPageBreak

getpagenumber = NumPage

 End Function