Page Numbers in Cells
DC appleyards
I have a monthly document (identical in appearance but not content) which
can expand or shrink dependant on the number of items which are added to it.
The top section is printed on each page and one of the cells needs to state
the page number… ie Page 1 of 2 etc.
Is there a formula that can be put in the cell that registers the printed
page number?
Dave
Dodo2u
<DCappl...@discussions.microsoft.com> wrote in
news:08DCFC3B-A680-454F...@microsoft.com:
Why not in the header?
View/Header and Footer/Custom header
Barb Reinhardt
the Header/Footer Tab. Select the pull down menu for header or footer and
page # is an option.
"DC appleyards" <DCappl...@discussions.microsoft.com> wrote in message
news:08DCFC3B-A680-454F...@microsoft.com...
> This is what I am doing....
>
> I have a monthly document (identical in appearance but not content) which
> can expand or shrink dependant on the number of items which are added to
> it.
> The top section is printed on each page and one of the cells needs to
> state
> the page number. ie Page 1 of 2 etc.
Barb Reinhardt
Use Headers or Footers to do this.
File -> Page Setup -> Header/Footer
On the pull down menu, there are several page options.
"DC appleyards" <DCappl...@discussions.microsoft.com> wrote in message
news:08DCFC3B-A680-454F...@microsoft.com...
> This is what I am doing....
>
> I have a monthly document (identical in appearance but not content) which
> can expand or shrink dependant on the number of items which are added to
> it.
> The top section is printed on each page and one of the cells needs to
> state
> the page number. ie Page 1 of 2 etc.
DC appleyards
Earl Kiosterud
(1) It appears as though you've laid your sheet out with regard to the
printed pages. That is a lot of trouble. If you haven't looked at the
printing features in File - Page setup, you should first do that. You can
have headings, including automatic page numbers, column and/or row headings
that automatically repeat across the pages, and other things. Now you just
maintain your worksheet as one continuous table, and let Page Setup add the
page stuff for printing.
(2) If you have to have the page numbers on the sheet, the next best
solution would be for a macro, fired by the Before_Print event, to loop
through the pages and put in the numbers into the cells where you want them.
It would have to walk its way through the page breaks, a slow process (the
last time I messed with it), as the PageBreak object is made of molasses.
And kept cold. If you're willing to put a macro in, along with its
ramifications (the user gets the "This workbook contains macros..." message
on opening), we'll write it for you.
(3) Failing that, you'll need to put the page numbers in yourself, into
cells. you'll need to determine where the page breaks are, then use
formulas for each page number. You can use View - Page Break Preview to see
that. Then you could pick a cell in each page, and use a formula that
refers to the prior page number cell. If the prior cell is A1, use:
=A1 + 1
But here's the problem: As you add and delete items, which presumably means
inserting and deleting rows, this will move down or up those page numbering
cells.
--
Earl Kiosterud
www.smokeylake.com
"DC appleyards" <DCappl...@discussions.microsoft.com> wrote in message
news:08DCFC3B-A680-454F...@microsoft.com...
> This is what I am doing....
>
> I have a monthly document (identical in appearance but not content) which
> can expand or shrink dependant on the number of items which are added to
> it.
> The top section is printed on each page and one of the cells needs to
> state
> the page number. ie Page 1 of 2 etc.
DC appleyards
If we put the macro into the document how much will it effect the printing
process?
I am keen to give it a go if it will not effect the document to badly...
Thanks for your help with this.
Dave
Earl Kiosterud
It won't affect printing at all. It will run automatically before the print
takes place, and will calculate and put page numbers in a cell in each page
of the sheet.
The following macro isn't complete. It should be put in a regular module
and gotten working as you want it before you put it in Before_Print for
automatic firing. It will put page numbers in the upper left corner cell of
each page, left pages only. If the worksheet is wider than one page, it
won't do them yet.
Sub pagenumbers()
Dim MyR As Range
Dim PageNumber As Long
Set Mysheet = ActiveSheet
PageNumber = 1
Set MyR = Range("A1") ' starting cell
MyR.Value = "Page " & PageNumber ' first page number
Do While Not Intersect(MyR, ActiveSheet.UsedRange) Is Nothing
If MyR.EntireRow.PageBreak = xlPageBreakAutomatic Then
MyR.Value = "Page " & PageNumber ' put page number in cell
PageNumber = PageNumber + 1
End If
Set MyR = MyR.Offset(1, 0) ' move down
Loop
End Sub
Run this on a copy of the sheet, as it will modify it. Make a new copy each
time you run it.
Consider carefully if you want to remain commited to this page-oriented
worksheet. You'll be precluding a lot of Excel functionality.
--
Earl Kiosterud
www.smokeylake.com
"DC appleyards" <DCappl...@discussions.microsoft.com> wrote in message
news:B5F16962-04EE-470E...@microsoft.com...
DC appleyards
Thanks for your help... I'll give it a go... but I think we might change the
format of the document.
Regards
DC
Earl Kiosterud
Now you're talking. Get familiar with the features in File - Page Setup.
It might just be what you need. If you want the printout in too different a
layout from the table (sheet), and have Access, you can use that for the
report (printout). It lets you lay the report any way you want -- Excel
prints it pretty much as is, with the features in Page Setup. You can
either move the whole application to Access (depending on what it is, that
may or may not be the way to go), or make a linked table to the existing
Excel sheet, which must be in table form, as I mentioned in the earlier
post.
--
Earl Kiosterud
www.smokeylake.com
"DC appleyards" <DCappl...@discussions.microsoft.com> wrote in message
news:5C39E4D4-512C-44B2...@microsoft.com...
Linker IT Software
I have added a function to my litLIB addin that does this. Perhaps you want
to have a look at it: www.oraxcel.com/projects/litlib
I have added the function =PageNumber and =PageCount
Dave Peterson
David McRitchie
are other solutions besides the one in the thread about forcing page breaks.
Here is one of the actual solutions Page # of ## into a cell. by Mrna Larson
http://groups.google.com/group/microsoft.public.excel.misc/msg/0fb2536c782464ff
part of this thread
http://groups.google.com/groups?threadm=379C8919.A9FE3A4B%40bowood.u-net.com
and a solution by Laurent Longre in this thread: using VBA
http://groups.google.com/groups?threadm=38fd6ebe%40news.server.worldonline.co.uk
--
FWIW
I had references to Page number, inserted into a cell, Laurent Longre, Myrna Larson.
but Google has messed them up. that's the who problem with them assigning their own
numbers or in reality recycling the old DejaVue numbers which Google had recognized
This is what I had the reference is good but the references within are bad.
http://groups.google.com/groups?threadm=OWLWQ2qlDHA.3312%40tk2msftngp13.phx.gbl
And I had to look up the references again, unfortunately I could only get the message
number form the first message in each of those threads.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dave Peterson" <pete...@verizonXSPAM.net> wrote in message news:4391B310...@verizonXSPAM.net...
Dave Peterson
Dave the message was intended.
Thanks for the posting the other solutions.
--
Dave Peterson
David McRitchie
There used to be a lot more of us in the Excel newsgroups.
Guess you can thank the people who don't know how to use newsgroups and
instead rely on Microsoft Communities or Excel Forum and who "think" it's appropriate
to use a handle instead of their name (don't get me started on Excel Forum).
What gets me is that only seems to be one Tom and only one Chip in the newsgroups (that I notice).
I guess Tom is not as frequent a name as I thought by Rank in the 1990 US Census
http://www.census.gov/genealogy/www/namesearch.html
shown as name, frequency, cumulative frequency, rank
for David, Tom, Dick, and Harry
male name freq. cumulative RANK
DAVID 2.363 17.176 6
RICHARD 1.703 18.878 7
THOMAS 1.380 23.185 10
HARRY 0.251 52.991 70
TOM 0.117 67.890 154
DAVE 0.053 77.316 271
DICK 0.009 87.298 721
The top ten male names in the US are
James, John, Robert, Michael, William, David, Richard, Charles, Joseph, Thomas
The top ten female names in the US are
Mary, Patricia, Linda, Barbara, Elizabeth, Jennifer, Maria, Susan, Margaret, Dorothy
but then that is probably how they sign their names or what is on birth certificates
If you want to see details for the top 10 names, or link to entire listings see
http://www.census.gov/genealogy/names/names_files.html
There is also a list somewhere for births for more current names.
--
"Dave Peterson" wrote
Dave Peterson
--
Dave Peterson
Ron Coderre
Check here:
http://babynamewizard.com/namevoyager/lnv0105.html
***********
Regards,
Ron
Dave Peterson
Thanks