Hi,
I need your help please.
My final goal is to create a table of contents, which contains section 1.0, 2.0, 2.1 etc. as well as page numbers. I have a big workbook with many worksheets.
Example:
Worksheet1 contains section 1.0
Worksheet2 contains section 2.0 and 2.1, 2.2 etc.
Worksheet3 contains section 3.0 and 3.1
Worksheet4 contains section 4.0, 4.1 and 4.2
Worksheet5 contains section 5.0
For eg., lets assume that worksheet 2 has 6 pages of data. Section 2.0 refers the first two pages in worksheet 'Sheet2'. Section 2.1 refers the pages 3 an 4 in worksheet 'Sheet2'. Section 2.2 referes pages 5 and 6.
In this case, the print area will be set for all 6 pages. However Section 2.0 refers first two and Section 2.1, refers the next two and section 2.2 refers the last two pages.
All right. Now, as I see it, the only way to control this, is to create some named ranges for each section (section 1.0, 2.0, 2.1, 2.2 etc) and have a macro to return the number of pages for each section (=return the number of pages for each named range). That way I’m able to calculate the pagenumber from there. However, I have other named ranges. So it need the macro only to return the pagenumber for named ranges that start with ex. “mps_” (macro page search).
If this isn’t possible, I’ll have to hard code in the names of the specific named ranges. That would be acceptable, as I rarely get new sections. I do not want the code to modify the print area.
So what I really need is the macro to output these data:
Sectionname No. of pages Sheet Name (named range)
Section 1.0 1 Sheet1 mps_Section1.0
Section 2.0 2 Sheet2 mps_Section2.0
Section 2.1 2 Sheet2 mps_Section2.1
Section 2.2 2 Sheet2 mps_Section2.2
Section 3.0 1 Sheet3 mps_Section3.0
Section 3.1 1 Sheet3 mps_Section3.1
Section 4.0 1 Sheet4 mps_Section4.0
Section 4.1 2 Sheet4 mps_Section4.1
Section 4.2 1 Sheet4 mps_Section4.2
Section 5.0 1 Sheet5 mps_Section5.0
Don’t mind the titles and don’t mind formatting (bold, adjustment etc.). I just want plain data and the most simple code.
What would be nice however, is if the sectionnames are hyperlinks to the sheets and they work even though the sheets are hidden.
The sectionnames should be the value from the upper left corner of each named range.
Is it possible?
Sorry for the long tread.
I need your help please.
My final goal is to create a table of contents, which contains section 1.0, 2.0, 2.1 etc. as well as page numbers. I have a big workbook with many worksheets.
Example:
Worksheet1 contains section 1.0
Worksheet2 contains section 2.0 and 2.1, 2.2 etc.
Worksheet3 contains section 3.0 and 3.1
Worksheet4 contains section 4.0, 4.1 and 4.2
Worksheet5 contains section 5.0
For eg., lets assume that worksheet 2 has 6 pages of data. Section 2.0 refers the first two pages in worksheet 'Sheet2'. Section 2.1 refers the pages 3 an 4 in worksheet 'Sheet2'. Section 2.2 referes pages 5 and 6.
In this case, the print area will be set for all 6 pages. However Section 2.0 refers first two and Section 2.1, refers the next two and section 2.2 refers the last two pages.
All right. Now, as I see it, the only way to control this, is to create some named ranges for each section (section 1.0, 2.0, 2.1, 2.2 etc) and have a macro to return the number of pages for each section (=return the number of pages for each named range). That way I’m able to calculate the pagenumber from there. However, I have other named ranges. So it need the macro only to return the pagenumber for named ranges that start with ex. “mps_” (macro page search).
If this isn’t possible, I’ll have to hard code in the names of the specific named ranges. That would be acceptable, as I rarely get new sections. I do not want the code to modify the print area.
So what I really need is the macro to output these data:
Sectionname No. of pages Sheet Name (named range)
Section 1.0 1 Sheet1 mps_Section1.0
Section 2.0 2 Sheet2 mps_Section2.0
Section 2.1 2 Sheet2 mps_Section2.1
Section 2.2 2 Sheet2 mps_Section2.2
Section 3.0 1 Sheet3 mps_Section3.0
Section 3.1 1 Sheet3 mps_Section3.1
Section 4.0 1 Sheet4 mps_Section4.0
Section 4.1 2 Sheet4 mps_Section4.1
Section 4.2 1 Sheet4 mps_Section4.2
Section 5.0 1 Sheet5 mps_Section5.0
Don’t mind the titles and don’t mind formatting (bold, adjustment etc.). I just want plain data and the most simple code.
What would be nice however, is if the sectionnames are hyperlinks to the sheets and they work even though the sheets are hidden.
The sectionnames should be the value from the upper left corner of each named range.
Is it possible?
Sorry for the long tread.
