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 |
How to count number of pages in a named range
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 |
How to count number of pages in a named range
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 |
All times are GMT +1. The time now is 02:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com