Page numbering - multiple tabs

[M A Lurker]

I have a workbook that has three tabs. I update the workbook throughout the
month, adding the previous day's data, analysis, and comments to what is
already there. I print the entire workbook each day and distribute hard
copies throughout the organization. I need to have all of the pages
distributed daily sequentially numbered.

The first day, each tab will generate one page, so the page numbers will be
1, 2, and 3. But each tab grows dynamically throughout the month so that in
the middle of the month, tab one may generate the first five pages, the
second tab may generate the next twelve pages, and the last tab will
generate seven pages. Next month, the relationship between pages and tabs
may vary, so that the number of pages per tab will be different.

I think you get the idea.

I know that I can manually go into settings and custom footer and enter a
predetermined offset for each tab to make, for example, the first page
generated by the second tab be page number 6, and the first page generated
by the third tab be page 18. But that requires me to figure out in advance
what the needed offset will be each day, and go in and manually change it
each day.

Is there a way to automatically do this? If there is, I haven't found it.

I imagine that I could manually enter page breaks, and include the page
number in a cell that prints with formulas, etc. But that's not what I'm
looking for. I'm hoping for some way to do this in the Custom Footer
setting.

Thanks in advance.

 

[Harlan Grove]

The first day, each tab will generate one page, so the page numbers
will be 1, 2, and 3. But each tab grows dynamically throughout the
month so that in the middle of the month, tab one may generate the
first five pages, the second tab may generate the next twelve pages,
and the last tab will generate seven pages. Next month, the
relationship between pages and tabs may vary, so that the number of
pages per tab will be different.

I think you get the idea.

....

My first suggestion would be to cheat. For worksheet 1, make the header or
footer 1.&[Page]; for worksheet 2, 2.&[Page]; and so on. If page numbers are
only needed to identify the sequence of pages, this would be sufficient, and
it's far & away the easiest approach.

If you really want sequential page numbers, then you could use VBA to count
page breaks on each worksheet. You should have 1 more page of printout than
you have page breaks on each worksheet, so the offset for worksheet 2 would
be 2 more than the number of page breaks on worksheet 1.

 

[M A Lurker]

Thanks for responding.

I had already considered the 1.&[Page] angle, and that is a viable
alternative that I could make work. However, I was trying to see if there
is something in Excel that I haven't discovered yet ... something like
@attrib(worksheetname,pages)+1. And I have totally made that up, but you
get the idea.

VBA is not an option as that is not currently included in my skill set. I
understand the opportunities, but have not taken the time to learn it yet.

The first day, each tab will generate one page, so the page numbers
will be 1, 2, and 3. But each tab grows dynamically throughout the
month so that in the middle of the month, tab one may generate the
first five pages, the second tab may generate the next twelve pages,
and the last tab will generate seven pages. Next month, the
relationship between pages and tabs may vary, so that the number of
pages per tab will be different.

I think you get the idea.

...

My first suggestion would be to cheat. For worksheet 1, make the header or
footer 1.&[Page]; for worksheet 2, 2.&[Page]; and so on. If page numbers
are
only needed to identify the sequence of pages, this would be sufficient,
and
it's far & away the easiest approach.

If you really want sequential page numbers, then you could use VBA to
count
page breaks on each worksheet. You should have 1 more page of printout
than
you have page breaks on each worksheet, so the offset for worksheet 2
would
be 2 more than the number of page breaks on worksheet 1.

 

[Mohamed Shafiee]

Hi there,

I have written a macro which does this.


'Macro
Sub SetPageNumbers()
Dim counter
Sheets(1).PageSetup.FirstPageNumber = 1
For i = 2 To ActiveWorkbook.Sheets.Count
counter = counter + (Sheets(i - 1).VPageBreaks.Count + 1) *
(Sheets(i - 1).HPageBreaks.Count + 1)
Sheets(i).PageSetup.FirstPageNumber = counter + 1
Next i
End Sub

'Usage
Private Sub Workbook_BeforePrint(Cancel As Boolean)
SetPageNumbers
End Sub


Hope that helped.

Shafiee.

 

[Earl Kiosterud]

M A,

If you're meaning you want the page numbers provided by the &[Page] code in
a header or footer to be one set of page sequential page numbers for all the
pages of all the sheets, setting "First page number" (File - Page setup) to
Auto in the sheets should give you what you want. You must print them as
one print job, either by using "Entire workbook" in the Print dialog, or by
having the sheets selected, then using "Selected sheets."

 

[M A Lurker]

AMAZING! Excel already has this built in and I defeated it by trying to
over-engineer the solution. Thank you.

And what is the most embarrassing is that is the default!

M A,

If you're meaning you want the page numbers provided by the &[Page] code
in a header or footer to be one set of page sequential page numbers for
all the pages of all the sheets, setting "First page number" (File - Page
setup) to Auto in the sheets should give you what you want. You must
print them as one print job, either by using "Entire workbook" in the
Print dialog, or by having the sheets selected, then using "Selected
sheets."

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I have a workbook that has three tabs. I update the workbook throughout
the month, adding the previous day's data, analysis, and comments to what
is already there. I print the entire workbook each day and distribute
hard copies throughout the organization. I need to have all of the pages
distributed daily sequentially numbered.

The first day, each tab will generate one page, so the page numbers will
be 1, 2, and 3. But each tab grows dynamically throughout the month so
that in the middle of the month, tab one may generate the first five
pages, the second tab may generate the next twelve pages, and the last
tab will generate seven pages. Next month, the relationship between
pages and tabs may vary, so that the number of pages per tab will be
different.

I think you get the idea.

I know that I can manually go into settings and custom footer and enter a
predetermined offset for each tab to make, for example, the first page
generated by the second tab be page number 6, and the first page
generated by the third tab be page 18. But that requires me to figure
out in advance what the needed offset will be each day, and go in and
manually change it each day.

Is there a way to automatically do this? If there is, I haven't found
it.

I imagine that I could manually enter page breaks, and include the page
number in a cell that prints with formulas, etc. But that's not what I'm
looking for. I'm hoping for some way to do this in the Custom Footer
setting.

Thanks in advance.


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

 

[Mohamed Shafiee]

AMAZING! Excel already has this built in and I defeated it by trying to
over-engineer the solution. Thank you.

And what is the most embarrassing is that is the default!

M A,

If you're meaning you want the page numbers provided by the &[Page] code
in a header or footer to be one set of page sequential page numbers for
all the pages of all the sheets, setting "First page number" (File - Page
setup) to Auto in the sheets should give you what you want. You must
print them as one print job, either by using "Entire workbook" in the
Print dialog, or by having the sheets selected, then using "Selected
sheets."

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

I have a workbook that has three tabs. I update the workbook throughout
the month, adding the previous day's data, analysis, and comments to what
is already there. I print the entire workbook each day and distribute
hard copies throughout the organization. I need to have all of the pages
distributed daily sequentially numbered.

The first day, each tab will generate one page, so the page numbers will
be 1, 2, and 3. But each tab grows dynamically throughout the month so
that in the middle of the month, tab one may generate the first five
pages, the second tab may generate the next twelve pages, and the last
tab will generate seven pages. Next month, the relationship between
pages and tabs may vary, so that the number of pages per tab will be
different.

I think you get the idea.

I know that I can manually go into settings and custom footer and enter
a predetermined offset for each tab to make, for example, the first page
generated by the second tab be page number 6, and the first page
generated by the third tab be page 18. But that requires me to figure
out in advance what the needed offset will be each day, and go in and
manually change it each day.

Is there a way to automatically do this? If there is, I haven't found
it.

I imagine that I could manually enter page breaks, and include the page
number in a cell that prints with formulas, etc. But that's not what
I'm looking for. I'm hoping for some way to do this in the Custom
Footer setting.

Thanks in advance.


----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----