How to count number of pages in a named range
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
|