Increment number in cell before printing
 

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

--
Message posted from http://www.ExcelForum.com

Increment number in cell before printing
 

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


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


"webfort " wrote in message ...
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 to
50. I have looked at all the examples that are dotted on thi site and
none of them work. Have tried either putting the code in the work book
and the sheet and still nothing. These are the ones I have found so
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

Ashley


---
Message posted from http://www.ExcelForum.com/

Increment number in cell before printing
 

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" wrote in message ...
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


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


"webfort " wrote in message ...
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 to
50. I have looked at all the examples that are dotted on thi site and
none of them work. Have tried either putting the code in the work book
and the sheet and still nothing. These are the ones I have found so
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

Ashley


---
Message posted from http://www.ExcelForum.com/

Increment number in cell before printing
 

Hi, where abouts do i put this code, am i putting it in teh right place
this is a screen shot.

Thanks

As

Attachment filename: screenshot.gif
Download attachment: http://www.excelforum.com/attachment.php?postid=50533
--
Message posted from http://www.ExcelForum.com

Increment number in cell before printing
 

Hi webfort

Where do I place the code ?

1. Alt-F11
2. InsertModule 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" wrote in message ...
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" wrote in message ...
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


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


"webfort " wrote in message ...
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 to
50. I have looked at all the examples that are dotted on thi site and
none of them work. Have tried either putting the code in the work book
and the sheet and still nothing. These are the ones I have found so
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

Ashley


---
Message posted from http://www.ExcelForum.com/

Increment number in cell before printing
 

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

--
Message posted from http://www.ExcelForum.com

Increment number in cell before printing
 

You are welcome

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


"webfort " wrote in message ...
Just wanted to say thanks fro the help, will def be book makring your
website, thanks for taking the time out of your day for help me, much
appreciated.

Thanks

Ash


---
Message posted from http://www.ExcelForum.com/

Increment number in cell before printing
 

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

Thanks As

--
Message posted from http://www.ExcelForum.com

Increment number in cell before printing
 

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


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


"webfort " wrote in message ...
Hi, would you know of a way to save this number after words so I could
start printing from that number.

Thanks Ash


---
Message posted from http://www.ExcelForum.com/

Increment number in cell before printing
 

Thanks, thats great.

As

--
Message posted from http://www.ExcelForum.com