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.
Please help
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