adding a page number in a cell

[DawnTreader]

i have found a little routine that almost does what i want here:

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

but what i need is for it to print one page and then the next page after
updating that cell. here is the code that i borrowed from that thread:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim AnySheet As Worksheet
Dim myRange As Range
Dim CellRange As Object

For Each AnySheet In ActiveWindow.SelectedSheets
'change "Sheet1" to actual sheet name
If AnySheet.Name = "Sheet1" Then
Set myRange = AnySheet.Range("I7")
For Each CellRange In myRange
CellRange.Value = CellRange.Value + 1
Next
End If
Next
'delete the next line to
'actually let it print out
'this is here just for testing
'Cancel = True
End Sub

it works in incrementing the cell, but it doesnt print the first page then
update the cell and print the second.

basically i need this for an invoice where the page number is located in the
middle of the page rather than in the header or footer.

 

[Bill Renaud]

<<i have found a little routine that almost does what i want here:>>
Your link refers to this post, as far as I can tell!

Anyway, maybe you mean to use something like the following. It puts a page
number in cell I7 on each of the selected worksheets starting at (page) 1.
This assumes that each worksheet is only 1 printed page in size.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Dim lngPageNumber As Long

lngPageNumber = 0

For Each ws In ActiveWindow.SelectedSheets
lngPageNumber = lngPageNumber + 1
ws.Range("I7").Value = lngPageNumber
Next ws
End Sub

 

[DawnTreader]

dang


i dont have multiple sheets. i have one sheet and i need the number to
increment after printing the first page. i have told the sheet to repeat rows
at the top so that the header information will be printed on both pages. now
in the future there may be more than 2 pages so any solution needs to have
the ability to increment print the page and then increment and print the page.

i will try out your mod to the code, but something tells me i didnt explain
myself well enough the first time.

 

[Bill Renaud]

<<i dont have multiple sheets. i have one sheet ...>>

This conflicts with your originally posted code. In it, you had "For Each
AnySheet In ActiveWindow.SelectedSheets".

<<i will try out your mod to the code, but something tells me i didnt
explain myself well enough the first time.>>

No need to try out the code, as it won't do what you want.

Tell us what you have for print headers (how many rows at the top of the
worksheet). This is now a more complex problem. Why not leave the Page
Numbers in the Header, instead of putting them in the printable area of the
document?

 

[DawnTreader]

Hello Bill

thanks for responding.

to quote myself "basically i need this for an invoice where the page number
is located in the middle of the page rather than in the header or footer."

i am trying to somewhat simplify our out of town invoice creation
spreadsheet. currently creating an invoice means duplicating the top half of
the first spreadsheet a number of times, after you figure out how many lines
you have that are past the bottom of the first printable page.

i figured if i was to just increment that cell when repeating rows and
printing then i could get it to be all on one sheet and still have multiple
pages.

cell I7 is the cell where i want it to cause the number to increase for each
page it will print. each page being what is created when the user adds line
items to the parts sold area.

like i had said i borrowed that code, i hadnt even modified it.

i am repeating 23 rows at the top of each page, and the cell i need to
increment is on row 7, inside of the repeating section.

any ideas and help is appreciated.

 

[Bill Renaud]

Try this routine. You can put it in a standard code module either in the
invoice workbook, or in a separate workbook. Call this routine from a
button somewhere or from the Tools|Macro|Macros command. It will put the
current page number in cell I7 before it prints each page. Make sure that
you have "Rows to repeat at top" in Page Setup correctly set to have your
common rows on the worksheet printed at the top of each printed page.

Public Sub PrintInvoice()
Dim wsActive As Worksheet
Dim rngPageNumber As Range
Dim lngPage As Long

Set wsActive = ActiveSheet
Set rngPageNumber = wsActive.Range("I7")

For lngPage = 1 To wsActive.HPageBreaks.Count + 1
rngPageNumber.Value = lngPage
wsActive.PrintOut From:=lngPage, _
To:=lngPage, _
Copies:=1
Next lngPage
End Sub

 

[Bill Renaud]

You might add the following line right at the end of the routine above the
End Sub line:

rngPageNumber.Value = 1

This will return the page number in cell I7 back to 1 when the printout is
finished, so it doesn't keep incrementing after each printout. (It won't
affect the next printout; it just makes the worksheet look better to an end
user.)

 

[DawnTreader]

Hello Bill

Worked... Aaawwwsome. Thanks a bunch.

 

[DawnTreader]

Hello

thanks again, however, i have one new question relating to the thing i am
using this code on.

i am trying to place a lot of text in the footer of the pages i print, so
much that excel tells me i am over the 255 limit. is there a way to reference
cells on a sheet to cause them to print at the bottom of each sheet.

i was thinking it would be nice if there was a way to use a code like the
&[Page] thingy for a footer to cause it to reference a cell. this would allow
me to put all the text i want in the cells and get it to print all the text i
need at the bottom of each page.

 

[DawnTreader]

Hello Again

i actually found a quick workaround. i made a picture of the thingy i needed
in the footer and used the pic to replace all the text. doesnt look half bad.

 

[Bill Renaud]

Glad that worked, as I know of no quick way to print cells at the bottom
(like a footer) of each page of a printout. You must not have hardly any
space to print stuff that changes on each printed page, unless you are
using tiny fonts!

 

[Ben Langhammer]

How could you get it to work if some worksheets are larger than 1 printed
page in size?

:

 

[Ben Langhammer]

figured it out... Had to modify part of what you said above, but it seems to
work.

Dim ws As Worksheet
Dim lngPageNumber As Long
Dim hPgBrks As Long

lngPageNumber = 0

For Each ws In ActiveWindow.SelectedSheets
hPgBrks = 0
If ws.HPageBreaks.Count = 0 Then
hPgBrks = 1
Else
hPgBrks = ws.HPageBreaks.Count + 1
End If
lngPageNumber = lngPageNumber + hPgBrks
ws.Range("Z1").Value = lngPageNumber
Next ws

Thanks for the great posts that helped me figure it out "on my own".

Ben

How could you get it to work if some worksheets are larger than 1 printed
page in size?

:

 

[Babs]

I have been trying to figure out, how to increase a number in a cell each
time you print it, for sometime! Now for the stupid question...Where do i
place this code?

Thanks in advance!
Barbara

figured it out... Had to modify part of what you said above, but it seems to
work.

Dim ws As Worksheet
Dim lngPageNumber As Long
Dim hPgBrks As Long

lngPageNumber = 0

For Each ws In ActiveWindow.SelectedSheets
hPgBrks = 0
If ws.HPageBreaks.Count = 0 Then
hPgBrks = 1
Else
hPgBrks = ws.HPageBreaks.Count + 1
End If
lngPageNumber = lngPageNumber + hPgBrks
ws.Range("Z1").Value = lngPageNumber
Next ws

Thanks for the great posts that helped me figure it out "on my own".

Ben

 

[Ben Langhammer]

You place the code in the visual basic part of the excel file. If you go to
the "Developer" tab in the ribbon, there should be an option for "Visual
Basic" (it is the left most button I think on the default ribbon for Excel
2007). Once in there, you want to put the code in the "ThisWorkbook"
section....

I hope that makes a bit of sense.

Ben