How to count number of pages in a named range

[rm81]

Hi,

I have an excel worksheet with a variable number of pages and with various
named ranges; for example "ABC" has 2 pages, "DEF" has four and so on. On
the last page of my worksheet I want to do a little table giving the number
of pages in each named range i.e.
"ABC" = 2 pages
"DEF" = 4 pages
"GHI" = 3 pages
Total = X pages

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?

I managed to get the code for the total number of pages even if it is a bit
messy :

Sub TotalNumberOfPages()
With ActiveSheet
.DisplayAutomaticPageBreaks = True
HorizBreaks = ActiveSheet.HPageBreaks.Count
HPages = HorizBreaks + 1
NumPages = HPages
.DisplayAutomaticPageBreaks = False
range("VERIFICATION_DU_DOSSIER").Cells(10, 1).Select
ActiveCell = NumPages
End With
End Sub

but VBA doesn't want to work when I try something for a named range :

..range("ABC") .DisplayAutomaticPageBreaks=True
HorizBreaks = ActiveSheet.range("ABC").HPageBreaks.Count
HPages = HorizBreaks + 1
NumPages = HPages
..range("ABC") .DisplayAutomaticPageBreaks = False
range("VERIFICATION_DU_DOSSIER").Cells(5, 1).Select
ActiveCell = NumPages

Now having looked in Help I think it's because DisplayAutomaticPageBreaks
can only apply to worksheets and not ranges. So does that mean I need to
find another way of coding it?

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

[Jim Thomlinson]

I am unaware of any method that will tell you haw many pages there are in a
range. This line of code

msgbox ExecuteExcel4Macro("get.document(50)")

Will tell you how many printed pager there are on the active sheet. Not sure
if that will help you or not...
--
HTH...

Jim Thomlinson


"rm81" wrote:

> Hi,
>
> I have an excel worksheet with a variable number of pages and with various
> named ranges; for example "ABC" has 2 pages, "DEF" has four and so on. On
> the last page of my worksheet I want to do a little table giving the number
> of pages in each named range i.e.
> "ABC" = 2 pages
> "DEF" = 4 pages
> "GHI" = 3 pages
> Total = X pages
>
> 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?
>
> I managed to get the code for the total number of pages even if it is a bit
> messy :
>
> Sub TotalNumberOfPages()
> With ActiveSheet
> .DisplayAutomaticPageBreaks = True
> HorizBreaks = ActiveSheet.HPageBreaks.Count
> HPages = HorizBreaks + 1
> NumPages = HPages
> .DisplayAutomaticPageBreaks = False
> range("VERIFICATION_DU_DOSSIER").Cells(10, 1).Select
> ActiveCell = NumPages
> End With
> End Sub
>
> but VBA doesn't want to work when I try something for a named range :
>
> .range("ABC") .DisplayAutomaticPageBreaks=True
> HorizBreaks = ActiveSheet.range("ABC").HPageBreaks.Count
> HPages = HorizBreaks + 1
> NumPages = HPages
> .range("ABC") .DisplayAutomaticPageBreaks = False
> range("VERIFICATION_DU_DOSSIER").Cells(5, 1).Select
> ActiveCell = NumPages
>
> Now having looked in Help I think it's because DisplayAutomaticPageBreaks
> can only apply to worksheets and not ranges. So does that mean I need to
> find another way of coding it?
>
> Hope this makes sense!!
> Thanks ever so much in advance for any contributions.
> Regards,
> rm81
>

[rm81]

Dear Jim,

Many thanks for your suggestion - had seen that code on some site but it
gives only the total number of printed pages on the sheet and not in the
different ranges. Will try and bash out some code this weekend then.

Thanks anyway! Have a good weekend
rm81

"Jim Thomlinson" wrote:

> I am unaware of any method that will tell you haw many pages there are in a
> range. This line of code
>
> msgbox ExecuteExcel4Macro("get.document(50)")
>
> Will tell you how many printed pager there are on the active sheet. Not sure
> if that will help you or not...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "rm81" wrote:
>
> > Hi,
> >
> > I have an excel worksheet with a variable number of pages and with various
> > named ranges; for example "ABC" has 2 pages, "DEF" has four and so on. On
> > the last page of my worksheet I want to do a little table giving the number
> > of pages in each named range i.e.
> > "ABC" = 2 pages
> > "DEF" = 4 pages
> > "GHI" = 3 pages
> > Total = X pages
> >
> > 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?
> >
> > I managed to get the code for the total number of pages even if it is a bit
> > messy :
> >
> > Sub TotalNumberOfPages()
> > With ActiveSheet
> > .DisplayAutomaticPageBreaks = True
> > HorizBreaks = ActiveSheet.HPageBreaks.Count
> > HPages = HorizBreaks + 1
> > NumPages = HPages
> > .DisplayAutomaticPageBreaks = False
> > range("VERIFICATION_DU_DOSSIER").Cells(10, 1).Select
> > ActiveCell = NumPages
> > End With
> > End Sub
> >
> > but VBA doesn't want to work when I try something for a named range :
> >
> > .range("ABC") .DisplayAutomaticPageBreaks=True
> > HorizBreaks = ActiveSheet.range("ABC").HPageBreaks.Count
> > HPages = HorizBreaks + 1
> > NumPages = HPages
> > .range("ABC") .DisplayAutomaticPageBreaks = False
> > range("VERIFICATION_DU_DOSSIER").Cells(5, 1).Select
> > ActiveCell = NumPages
> >
> > Now having looked in Help I think it's because DisplayAutomaticPageBreaks
> > can only apply to worksheets and not ranges. So does that mean I need to
> > find another way of coding it?
> >
> > Hope this makes sense!!
> > Thanks ever so much in advance for any contributions.
> > Regards,
> > rm81
> >