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.



----== 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 =----

[Harlan Grove]

"M A Lurker" <NoWay1025@newsfeeds.com> wrote...
....
>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.


"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:eXOZx9ZNFHA.3512@TK2MSFTNGP15.phx.gbl...
> "M A Lurker" <NoWay1025@newsfeeds.com> wrote...
> ...
>>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.
>
>



----== 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]

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.


"M A Lurker" <NoWay1025@newsfeeds.com> wrote in message
news:424b5758$1_1@127.0.0.1...
>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
> =----

[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."

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

"M A Lurker" <NoWay1025@newsfeeds.com> wrote in message
news:424b5758$1_1@127.0.0.1...
>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
> =----

[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!