Creating a counter for printing
 

Hi,
I need to be able to create a counter so that when a value is entered into a
cell (for the number of copies to be printed), excel adds this number
incrementally to a base value so that each sheet printed (all of which are
identical except for their reference number) have a different number.
For example...
Base value = 100
Copies printed = 3
So the first sheet prints with a value of 101, the second 102, the third
103.
I then need excel to remember that the last sheet number was 103, so that
next time the workbook is opened, the base value will start at 103.

Any suggestions?
Regards
Colin Foster

Creating a counter for printing
 

Sub PRINTME()
If Range("B1").Value = 0 Then
MsgBox "Please enter the number of copies to print."
Range("B1").Select
Exit Sub
End If
Do Until Range("B1").Value = 0
Range("A1").Value = Range("A1").Value + 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B1").Value = Range("B1").Value - 1
Loop
End Sub

You would need to adjust this to suit your needs, In range B1 you would put
the number of copies you want to print, ( so you did not have to run the
macro for each sheet that you wanted to print ) and in range A1 I would
place the number 100 ( for your base number ) I would then link range A1 to
the cell that you wanted to show the print number then column A.

build a print button to attach the macro to

hope it helps
regards Pete

--
(][ This Email has been scanned by Norton AntiVirus. ][)
"Colin Foster" wrote in message
...
Hi,
I need to be able to create a counter so that when a value is entered into
a cell (for the number of copies to be printed), excel adds this number
incrementally to a base value so that each sheet printed (all of which are
identical except for their reference number) have a different number.
For example...
Base value = 100
Copies printed = 3
So the first sheet prints with a value of 101, the second 102, the third
103.
I then need excel to remember that the last sheet number was 103, so that
next time the workbook is opened, the base value will start at 103.

Any suggestions?
Regards
Colin Foster

Creating a counter for printing
 

Hi Colin

Try this

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


"Colin Foster" wrote in message ...
Hi,
I need to be able to create a counter so that when a value is entered into a cell (for the number of copies to be printed), excel
adds this number incrementally to a base value so that each sheet printed (all of which are identical except for their reference
number) have a different number.
For example...
Base value = 100
Copies printed = 3
So the first sheet prints with a value of 101, the second 102, the third 103.
I then need excel to remember that the last sheet number was 103, so that next time the workbook is opened, the base value will
start at 103.

Any suggestions?
Regards
Colin Foster

Creating a counter for printing
 

Pete,
Thanks for this... tried it & it works a treat - sorry for the delay in
responding - been away from the office.
Regards
Colin Foster
"OZDOC1050" wrote in message
...
Sub PRINTME()
If Range("B1").Value = 0 Then
MsgBox "Please enter the number of copies to print."
Range("B1").Select
Exit Sub
End If
Do Until Range("B1").Value = 0
Range("A1").Value = Range("A1").Value + 1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("B1").Value = Range("B1").Value - 1
Loop
End Sub

You would need to adjust this to suit your needs, In range B1 you would
put the number of copies you want to print, ( so you did not have to run
the macro for each sheet that you wanted to print ) and in range A1 I
would place the number 100 ( for your base number ) I would then link
range A1 to the cell that you wanted to show the print number then column
A.

build a print button to attach the macro to

hope it helps
regards Pete

--
(][ This Email has been scanned by Norton AntiVirus. ][)
"Colin Foster" wrote in message
...
Hi,
I need to be able to create a counter so that when a value is entered
into a cell (for the number of copies to be printed), excel adds this
number incrementally to a base value so that each sheet printed (all of
which are identical except for their reference number) have a different
number.
For example...
Base value = 100
Copies printed = 3
So the first sheet prints with a value of 101, the second 102, the third
103.
I then need excel to remember that the last sheet number was 103, so that
next time the workbook is opened, the base value will start at 103.

Any suggestions?
Regards
Colin Foster

Creating a counter for printing
 

Hi Ron,
Thanks for this option - in the event, I used Pete's option, but I'm keeping
a note of both for future use.
Sorry for th edelay in responding - been away from the office
Regards
Colin Foster
"Ron de Bruin" wrote in message
...
Hi Colin

Try this

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


"Colin Foster" wrote in message
...
Hi,
I need to be able to create a counter so that when a value is entered
into a cell (for the number of copies to be printed), excel adds this
number incrementally to a base value so that each sheet printed (all of
which are identical except for their reference number) have a different
number.
For example...
Base value = 100
Copies printed = 3
So the first sheet prints with a value of 101, the second 102, the third
103.
I then need excel to remember that the last sheet number was 103, so that
next time the workbook is opened, the base value will start at 103.

Any suggestions?
Regards
Colin Foster