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

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

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

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

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

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