numbering copies

[Kevin]

I need to print multiple copies of a spreadsheet and with
each copy change the numberic value of a cell equivalent
to the number of copies. I mean that the first copy will
have number "1" in the cell and increment up so that the
tenth copy will have the number "10" in the cell.
Thanks in advance for any ideas.

 

[Ron de Bruin]

Hi Kevin

Try this
http://www.rondebruin.nl/print.htm#number

 

[Kevin]

Thanks for the answer Ron,
Where do I add that code? Under a command button?

-----Original Message-----
Hi Kevin

Try this
http://www.rondebruin.nl/print.htm#number

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Kevin" <[email protected]> wrote in

message news:[email protected]...

 

[Kevin]

Ron,
That works but I want the count to start with the
number I have entered in the cell "A1" and increment from
there and I only want the number value returned not
the "of ?". It would be like creating a serial number for
each page.
What do you think?

Kevin

-----Original Message-----
Hi Kevin

Try this
http://www.rondebruin.nl/print.htm#number

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Kevin" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Try this then

Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

With ActiveSheet
For CopieNumber = .Range("a1").Value To CopiesCount + .Range("a1").Value

'number in cell A1
.Range("a1").Value = CopieNumber

'number in the footer
'.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

'Print the sheet
.PrintOut
Next CopieNumber
End With
End Sub

 

[Ron de Bruin]

Alt-F11
Insert>Module from the menu bar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select the macro and press Run

You can also add a button on your sheet and assign this macro to it.
If you need help with that post back

 

[Kevin]

Ron, that works great. Thanks so much.

-----Original Message-----
Try this then

Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long
CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

With ActiveSheet
For CopieNumber = .Range("a1").Value To

CopiesCount + .Range("a1").Value

'number in cell A1
.Range("a1").Value = CopieNumber

'number in the footer
'.PageSetup.LeftFooter = CopieNumber & " of " & CopiesCount

'Print the sheet
.PrintOut
Next CopieNumber
End With
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Kevin" <[email protected]> wrote in

message news:[email protected]...