ascending page numbers for 300 page printout of 1 page?

K

Kane3030

i have one page i have to print in excel but i need to print 300 copie
and each page needs an ascending number from the previous page.

i.e. the first page should say 1, the second 2, etc.

is there any way to do this without making 300 copy and pastes of th
same sheet for the numbering
 
J

JE McGimpsey

Where do you want the copy number to be printed?

If in a header, put something like this in the ThisWorkbook code module
of your workbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim vResult As Variant
Dim i As Long
Cancel = True
Do
vResult = Application.InputBox( _
Prompt:="Number of copies:", _
Title:="Serialized Print", _
Type:=1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until CLng(vResult) > 0
Application.EnableEvents = False
For i = 1 To vResult
With ActiveSheet
.PageSetup.RightHeader = _
"Copy #" & Format(i, "000")
.PrintOut
End With
Next i
Application.EnableEvents = True
End Sub

if you're not familiar with macros, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
O

Otto Moehrbach

Kane
One way would be to write a macro with a repeating print command with a
cell number incrementing with each cycle. Something like this (untested):
Sub Print300
Dim RangeToPrint as Range
Dim c As Long 'Cycle counter
Set RangeToPrint = 'Define your print range
For c = 1 To 300
Range("K50") = c 'The page number cell
RangeToPrint.PrintOut
Next
End Sub

HTH Otto
 
K

Kane3030

JE said:
*Where do you want the copy number to be printed?

If in a header, put something like this in the ThisWorkbook cod
module
of your workbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim vResult As Variant
Dim i As Long
Cancel = True
Do
vResult = Application.InputBox( _
Prompt:="Number of copies:", _
Title:="Serialized Print", _
Type:=1)
If vResult = False Then Exit Sub 'user cancelled
Loop Until CLng(vResult) > 0
Application.EnableEvents = False
For i = 1 To vResult
With ActiveSheet
.PageSetup.RightHeader = _
"Copy #" & Format(i, "000")
.PrintOut
End With
Next i
Application.EnableEvents = True
End Sub

if you're not familiar with macros, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...

i had to slightly modify your code but thank you very much! i haven'
touched micros in years and almost forgot you could do things lik
this
 
Top