Increment number in cell before printing

W

webfort

Hi, does any one know how to Increment number in cell before printing
eg if i want 50 copies of a sheet the number will increment from 1 t
50. I have looked at all the examples that are dotted on thi site an
none of them work. Have tried either putting the code in the work boo
and the sheet and still nothing. These are the ones I have found s
far:-


'Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Worksheets("Sheet1").Select
'Range("A1").Value = Range("A1") + 1
'ActiveSheet.PageSetup.CenterFooter = Range("G3").Value = Range("G3")
1
'End Sub

'Private Sub Workbook_Open()
'Worksheets("Sheet1").Select
'Range("A1").Value = Range("A1") + 1
'End Sub

'Sub TestMe()
'Dim HowMany As Integer
'Dim StartNum As Integer
'If Range("A1").Value = "" Then Range("A1").Value = 1
'HowMany = InputBox("How many Invoices to print starting at " &
'Range("A1").Value)
'StartNum = Range("A1").Value
'Dim i As Integer
'For i = StartNum To StartNum + HowMany - 1
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'Range("A1").Value = Range("A1").Value + 1
'Next i
'End Sub

'Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Worksheets("Sheet1").Range("A1").Value = _
'Worksheets("Sheet1").Range("A1").Value + 1
'End Sub



Any ideas

Thanks

Ashle
 
R

Ron de Bruin

Hi webfort

Try this

Sub test()
Dim a As Integer
For a = 1 To 50
Range("a1").Value = a & " of 50"
ActiveSheet.PrintOut
Next a
End Sub

Or in the Footer

Sub PrintCopies()
Dim i As Long
For i = 1 To 50
With ActiveSheet
.PageSetup.LeftFooter = i
.PrintOut
End With
Next
End Sub
 
R

Ron de Bruin

With a InputBox you can do this
I add the macro to my Print Tips Page today
http://www.rondebruin.nl/print.htm


Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long

CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

For CopieNumber = 1 To CopiesCount
With ActiveSheet
'number in cell A1
.Range("a1").Value = CopieNumber & " of " & CopiesCount

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

.PrintOut
End With
Next CopieNumber
End Sub


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


Ron de Bruin said:
Hi webfort

Try this

Sub test()
Dim a As Integer
For a = 1 To 50
Range("a1").Value = a & " of 50"
ActiveSheet.PrintOut
Next a
End Sub

Or in the Footer

Sub PrintCopies()
Dim i As Long
For i = 1 To 50
With ActiveSheet
.PageSetup.LeftFooter = i
.PrintOut
End With
Next
End Sub
Click to expand...
 
R

Ron de Bruin

Hi webfort

Where do I place the code ?

1. Alt-F11
2. Insert>Module from the Menu Bar
3. Paste the Code there
4. Alt-Q to go back to Excel
5. Alt-F8 to select and run the sub


Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long

CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

For CopieNumber = 1 To CopiesCount
With ActiveSheet
'number in cell A1
.Range("a1").Value = CopieNumber & " of " & CopiesCount

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

.PrintOut
End With
Next CopieNumber
End Sub


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


Ron de Bruin said:
With a InputBox you can do this
I add the macro to my Print Tips Page today
http://www.rondebruin.nl/print.htm


Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopieNumber As Long

CopiesCount = Application.InputBox("How many Copies do you want", Type:=1)

For CopieNumber = 1 To CopiesCount
With ActiveSheet
'number in cell A1
.Range("a1").Value = CopieNumber & " of " & CopiesCount

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

.PrintOut
End With
Next CopieNumber
End Sub
Click to expand...
 
W

webfort

Just wanted to say thanks fro the help, will def be book makring you
website, thanks for taking the time out of your day for help me, muc
appreciated.

Thanks

As
 
W

webfort

Hi, would you know of a way to save this number after words so I coul
start printing from that number.

Thanks As
 
R

Ron de Bruin

Try this

It will use the value in A1 +1 to start the new number

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 + 1 To CopiesCount + .Range("a1").Value
.Range("a1").Value = CopieNumber
.Printout
Next CopieNumber
End With
End Sub
 
Top