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

http://www.microsoft.com/office/comm...xp=&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.

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

--
Regards,
Bill Renaud

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" wrote:

> <<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
>
> --
> Regards,
> 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?

--
Regards,
Bill Renaud

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" wrote:

> <<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?
>
> --
> Regards,
> 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

--
Regards,
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.)
--
Regards,
Bill Renaud

Hello Bill

Worked... Aaawwwsome. Thanks a bunch.

"Bill Renaud" wrote:

> 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.)
> --
> Regards,
> Bill Renaud
>
>
>
>

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.

"Bill Renaud" wrote:

> 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.)
> --
> Regards,
> Bill Renaud
>
>
>
>

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" wrote:

> 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.)
> --
> Regards,
> Bill Renaud
>
>
>
>