Counting pages for tab
 

G'day All

Would anyone know the macro to count the number of pages each that would
print out for each tab.

For example you had 3 tabs.
First one is the summary page (where the answer to this question goes)
Second one has data in it and prints out 5 pages
Third one has 3 pages.

So the answer would be (assuming starting at A1)
1
5
3

Thanks in advance.

Counting pages for tab
 

Based on the code shown he http://www.ozgrid.com/VBA/printed-pages.htm
Modified, we can come up with this macro

Sub HowManyPagesBreaks()
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer

iHpBreaks = ActiveSheet.HPageBreaks.Count + 1
iVBreaks = ActiveSheet.VPageBreaks.Count + 1
iTotPages = iHpBreaks * iVBreaks

Range("A1") = iTotPages
'get 2nd sheet's page count
'change Sheet2 to actual tab name
iHpBreaks = Worksheets("Sheet2").HPageBreaks.Count + 1
iVBreaks = Worksheets("Sheet2").VPageBreaks.Count + 1
Range("A2") = iHpBreaks * iVBreaks
'get 3rd sheet's page count
'change Sheet3 to actual tab name
iHpBreaks = Worksheets("Sheet3").HPageBreaks.Count + 1
iVBreaks = Worksheets("Sheet3").VPageBreaks.Count + 1
Range("A3") = iHpBreaks * iVBreaks
End Sub

"Art Vandelay" wrote:

G'day All

Would anyone know the macro to count the number of pages each that would
print out for each tab.

For example you had 3 tabs.
First one is the summary page (where the answer to this question goes)
Second one has data in it and prints out 5 pages
Third one has 3 pages.

So the answer would be (assuming starting at A1)
1
5
3

Thanks in advance.

Counting pages for tab
 

Thanks JLatham,

It wasn't what I was exactly after, but it did point me in the right
direction.
So thanks, for without your help I would still be floundering.
If I get the whole thing working I might post it for everyone.
Basically I'm trying to create a Table of Contents with page (not tabs)
numbers that work with a variable number of tabs.

"JLatham" wrote:

Based on the code shown he http://www.ozgrid.com/VBA/printed-pages.htm
Modified, we can come up with this macro

Sub HowManyPagesBreaks()
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer

iHpBreaks = ActiveSheet.HPageBreaks.Count + 1
iVBreaks = ActiveSheet.VPageBreaks.Count + 1
iTotPages = iHpBreaks * iVBreaks

Range("A1") = iTotPages
'get 2nd sheet's page count
'change Sheet2 to actual tab name
iHpBreaks = Worksheets("Sheet2").HPageBreaks.Count + 1
iVBreaks = Worksheets("Sheet2").VPageBreaks.Count + 1
Range("A2") = iHpBreaks * iVBreaks
'get 3rd sheet's page count
'change Sheet3 to actual tab name
iHpBreaks = Worksheets("Sheet3").HPageBreaks.Count + 1
iVBreaks = Worksheets("Sheet3").VPageBreaks.Count + 1
Range("A3") = iHpBreaks * iVBreaks
End Sub

"Art Vandelay" wrote:

G'day All

Would anyone know the macro to count the number of pages each that would
print out for each tab.

For example you had 3 tabs.
First one is the summary page (where the answer to this question goes)
Second one has data in it and prints out 5 pages
Third one has 3 pages.

So the answer would be (assuming starting at A1)
1
5
3

Thanks in advance.

Counting pages for tab
 

Thanks JLatham,

It wasn't what I was exactly after, but it did point me in the right
direction.
So thanks, for without your help I would still be floundering.
If I get the whole thing working I might post it for everyone.
Basically I'm trying to create a Table of Contents with page (not tabs)
numbers that work with a variable number of tabs.

"JLatham" wrote:

Based on the code shown he http://www.ozgrid.com/VBA/printed-pages.htm
Modified, we can come up with this macro

Sub HowManyPagesBreaks()
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer

iHpBreaks = ActiveSheet.HPageBreaks.Count + 1
iVBreaks = ActiveSheet.VPageBreaks.Count + 1
iTotPages = iHpBreaks * iVBreaks

Range("A1") = iTotPages
'get 2nd sheet's page count
'change Sheet2 to actual tab name
iHpBreaks = Worksheets("Sheet2").HPageBreaks.Count + 1
iVBreaks = Worksheets("Sheet2").VPageBreaks.Count + 1
Range("A2") = iHpBreaks * iVBreaks
'get 3rd sheet's page count
'change Sheet3 to actual tab name
iHpBreaks = Worksheets("Sheet3").HPageBreaks.Count + 1
iVBreaks = Worksheets("Sheet3").VPageBreaks.Count + 1
Range("A3") = iHpBreaks * iVBreaks
End Sub

"Art Vandelay" wrote:

G'day All

Would anyone know the macro to count the number of pages each that would
print out for each tab.

For example you had 3 tabs.
First one is the summary page (where the answer to this question goes)
Second one has data in it and prints out 5 pages
Third one has 3 pages.

So the answer would be (assuming starting at A1)
1
5
3

Thanks in advance.

Counting pages for tab
 

If you need more help, get in touch directly via email - the system here
hasn't notified me of a response to a post in weeks. Remove spaces:
Help From @ JLatham Site.com

"Art Vandelay" wrote:

Thanks JLatham,

It wasn't what I was exactly after, but it did point me in the right
direction.
So thanks, for without your help I would still be floundering.
If I get the whole thing working I might post it for everyone.
Basically I'm trying to create a Table of Contents with page (not tabs)
numbers that work with a variable number of tabs.

"JLatham" wrote:

Based on the code shown he http://www.ozgrid.com/VBA/printed-pages.htm
Modified, we can come up with this macro

Sub HowManyPagesBreaks()
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer

iHpBreaks = ActiveSheet.HPageBreaks.Count + 1
iVBreaks = ActiveSheet.VPageBreaks.Count + 1
iTotPages = iHpBreaks * iVBreaks

Range("A1") = iTotPages
'get 2nd sheet's page count
'change Sheet2 to actual tab name
iHpBreaks = Worksheets("Sheet2").HPageBreaks.Count + 1
iVBreaks = Worksheets("Sheet2").VPageBreaks.Count + 1
Range("A2") = iHpBreaks * iVBreaks
'get 3rd sheet's page count
'change Sheet3 to actual tab name
iHpBreaks = Worksheets("Sheet3").HPageBreaks.Count + 1
iVBreaks = Worksheets("Sheet3").VPageBreaks.Count + 1
Range("A3") = iHpBreaks * iVBreaks
End Sub

"Art Vandelay" wrote:

G'day All

Would anyone know the macro to count the number of pages each that would
print out for each tab.

For example you had 3 tabs.
First one is the summary page (where the answer to this question goes)
Second one has data in it and prints out 5 pages
Third one has 3 pages.

So the answer would be (assuming starting at A1)
1
5
3

Thanks in advance.