Count number of pages

Hi there,

I have an excel document with a variable number of pages and with various
named ranges. On the last page of my document I want to give the number of
pages in each named range i.e.
"ABC" = 2 pages
"DEF" = 4 pages
"GHI" = 3 pages etc

Is there a function or formula that exists that returns the number of pages
in these named ranges i.e. cell A500 = NBPAGES(range("ABC")) ?

If not, I presume I would have to do a macro. Taking into account the fact
that the result cells addresses are variable, does anyone have an idea of
what the code might be like?

Hope this makes sense!!
Thanks ever so much in advance for any contributions.
Regards,
rm81

This is a macro example using the 'ABC' range.
'/==============================================/
' Sub Purpose: Get the number of print pages in PrintArea of
' active worksheet
'
Sub GetPageCount()
Dim iView As Integer
Dim iHorizontalBreaks As Integer
Dim iVerticalBreaks As Integer
Dim iPageCount As Integer

On Error Resume Next

'go to the range and set PrintArea to the range name
Application.Goto Reference:="ABC"
ActiveSheet.PageSetup.PrintArea = Selection.Address

'turn monitor flickering/updating off
Application.ScreenUpdating = False

'remember current View setting
iView = Application.ActiveWindow.View

'refresh the print area
Application.ActiveWindow.View = xlPageBreakPreview

'set view back
Application.ActiveWindow.View = xlNormalView

'turn monitor flickering/updating back on
Application.ScreenUpdating = True

'calculate the # of pages
iHorizontalBreaks = ActiveSheet.HPageBreaks.Count + 1
iVerticalBreaks = ActiveSheet.VPageBreaks.Count + 1
iPageCount = iHorizontalBreaks * iVerticalBreaks

MsgBox iPageCount

End Sub
'/==============================================/
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"rm81" wrote:

> Hi there,
>
> I have an excel document with a variable number of pages and with various
> named ranges. On the last page of my document I want to give the number of
> pages in each named range i.e.
> "ABC" = 2 pages
> "DEF" = 4 pages
> "GHI" = 3 pages etc
>
> Is there a function or formula that exists that returns the number of pages
> in these named ranges i.e. cell A500 = NBPAGES(range("ABC")) ?
>
> If not, I presume I would have to do a macro. Taking into account the fact
> that the result cells addresses are variable, does anyone have an idea of
> what the code might be like?
>
> Hope this makes sense!!
> Thanks ever so much in advance for any contributions.
> Regards,
> rm81

'set view back
Application.ActiveWindow.View = xlNormalView

SHOULD BE...
'set view back
Application.ActiveWindow.View = iView

Sorry about that.
Sincerely,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Gary L Brown" wrote:

> This is a macro example using the 'ABC' range.
> '/==============================================/
> ' Sub Purpose: Get the number of print pages in PrintArea of
> ' active worksheet
> '
> Sub GetPageCount()
> Dim iView As Integer
> Dim iHorizontalBreaks As Integer
> Dim iVerticalBreaks As Integer
> Dim iPageCount As Integer
>
> On Error Resume Next
>
> 'go to the range and set PrintArea to the range name
> Application.Goto Reference:="ABC"
> ActiveSheet.PageSetup.PrintArea = Selection.Address
>
> 'turn monitor flickering/updating off
> Application.ScreenUpdating = False
>
> 'remember current View setting
> iView = Application.ActiveWindow.View
>
> 'refresh the print area
> Application.ActiveWindow.View = xlPageBreakPreview
>
> 'set view back
> Application.ActiveWindow.View = xlNormalView
>
> 'turn monitor flickering/updating back on
> Application.ScreenUpdating = True
>
> 'calculate the # of pages
> iHorizontalBreaks = ActiveSheet.HPageBreaks.Count + 1
> iVerticalBreaks = ActiveSheet.VPageBreaks.Count + 1
> iPageCount = iHorizontalBreaks * iVerticalBreaks
>
> MsgBox iPageCount
>
> End Sub
> '/==============================================/
> HTH,
> --
> Gary Brown
> gary_brown@ge_NOSPAM.com
> If this post was helpful, please click the ''Yes'' button next to ''Was this
> Post Helpfull to you?''.
>
>
> "rm81" wrote:
>
> > Hi there,
> >
> > I have an excel document with a variable number of pages and with various
> > named ranges. On the last page of my document I want to give the number of
> > pages in each named range i.e.
> > "ABC" = 2 pages
> > "DEF" = 4 pages
> > "GHI" = 3 pages etc
> >
> > Is there a function or formula that exists that returns the number of pages
> > in these named ranges i.e. cell A500 = NBPAGES(range("ABC")) ?
> >
> > If not, I presume I would have to do a macro. Taking into account the fact
> > that the result cells addresses are variable, does anyone have an idea of
> > what the code might be like?
> >
> > Hope this makes sense!!
> > Thanks ever so much in advance for any contributions.
> > Regards,
> > rm81

Dear Gary,

Thanks very much for that code - will try it out this weekend.

Have a good weekend yourself
rm81

"Gary L Brown" wrote:

> 'set view back
> Application.ActiveWindow.View = xlNormalView
>
> SHOULD BE...
> 'set view back
> Application.ActiveWindow.View = iView
>
> Sorry about that.
> Sincerely,
> --
> Gary Brown
> gary_brown@ge_NOSPAM.com
> If this post was helpful, please click the ''Yes'' button next to ''Was this
> Post Helpfull to you?''.
>
>
> "Gary L Brown" wrote:
>
> > This is a macro example using the 'ABC' range.
> > '/==============================================/
> > ' Sub Purpose: Get the number of print pages in PrintArea of
> > ' active worksheet
> > '
> > Sub GetPageCount()
> > Dim iView As Integer
> > Dim iHorizontalBreaks As Integer
> > Dim iVerticalBreaks As Integer
> > Dim iPageCount As Integer
> >
> > On Error Resume Next
> >
> > 'go to the range and set PrintArea to the range name
> > Application.Goto Reference:="ABC"
> > ActiveSheet.PageSetup.PrintArea = Selection.Address
> >
> > 'turn monitor flickering/updating off
> > Application.ScreenUpdating = False
> >
> > 'remember current View setting
> > iView = Application.ActiveWindow.View
> >
> > 'refresh the print area
> > Application.ActiveWindow.View = xlPageBreakPreview
> >
> > 'set view back
> > Application.ActiveWindow.View = xlNormalView
> >
> > 'turn monitor flickering/updating back on
> > Application.ScreenUpdating = True
> >
> > 'calculate the # of pages
> > iHorizontalBreaks = ActiveSheet.HPageBreaks.Count + 1
> > iVerticalBreaks = ActiveSheet.VPageBreaks.Count + 1
> > iPageCount = iHorizontalBreaks * iVerticalBreaks
> >
> > MsgBox iPageCount
> >
> > End Sub
> > '/==============================================/
> > HTH,
> > --
> > Gary Brown
> > gary_brown@ge_NOSPAM.com
> > If this post was helpful, please click the ''Yes'' button next to ''Was this
> > Post Helpfull to you?''.
> >
> >
> > "rm81" wrote:
> >
> > > Hi there,
> > >
> > > I have an excel document with a variable number of pages and with various
> > > named ranges. On the last page of my document I want to give the number of
> > > pages in each named range i.e.
> > > "ABC" = 2 pages
> > > "DEF" = 4 pages
> > > "GHI" = 3 pages etc
> > >
> > > Is there a function or formula that exists that returns the number of pages
> > > in these named ranges i.e. cell A500 = NBPAGES(range("ABC")) ?
> > >
> > > If not, I presume I would have to do a macro. Taking into account the fact
> > > that the result cells addresses are variable, does anyone have an idea of
> > > what the code might be like?
> > >
> > > Hope this makes sense!!
> > > Thanks ever so much in advance for any contributions.
> > > Regards,
> > > rm81