• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get page number of a cell in excel 2010 macro

uloveonce

New Member
Hello,

I have googled for the question but could not find a perfect answer for my questions. There are some but in lots if different codes.

For accounting purpose, I have a cell in tabs which can be on any tabs, 5 to 10 tabs. I have format the print on each page and I need to print the whole files as one package. One tb may have 1 sheet while other may have 10. Issue is that there is no proper code to get me the page number for any values that is selected.
e.g.
tab 1 has 1 sheet only
tab 2 has 1 sheet only
tab 3 has 3 sheet only
tab 4 has 10 sheet only
tab 5 has 1 sheet only
tab 6 has 3 sheet only

If I am on 4 tab and print wise on 3 sheet, It should give me 1+1+3+3=8

There is no fixed sheets in one tab. It may change every week or month and have more than 60 such files.

One good thing is pages to be printed is always one below another. (vertical)
Fit all columns in one page option.

I have a code which give current page number but does not add previous page numbers to it.

Code:
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


Please help
 
Uloveonce

Firstly, Welcome to the Chandoo.org Forums

You will need to count the number of pages and assign page numbers to do what you want.

Have a read of
http://www.ozgrid.com/VBA/printed-pages.htm

So If you want to assign a page a specific page number you will need to keep track of the previous page counts and then print each page one by one on multiple page worksheets, assigning a new page number via code as you go
 
Thanks, i will try it out. I had thought of this, but it seems very lengthy coding and will take some time with skills
 
Back
Top