Auto increment & print three copies

[Stephen]

Hi Folks,

I have a little macro that will prompt a user for the number of copies they
want of the given sheet (only one in the workbook), then print three copies
of it (for three-part carbonless paper) then increment to the next number
(the sheet has a numberical value and print another three copies. The
problem I have is each time I'm done running the macro I have to go back into
the code and manually update the incrementing number.

What I would like to do is have the macro look to a particular cell to know
what number to start with and increment.

Here is what I'm currently working with...

' Copy Count (as of last run) = 270001
Sub PrintCopies_ActiveSheet()
Dim CopiesCount As Long
Dim CopyNumber As Long
CopiesCount = Application.InputBox("How many copies do you want???",
Type:=1)

For CopyNumber = 1 To CopiesCount
With ActiveSheet
'number in cell I1
.Range("I1").Value = 270001 + CopyNumber

'Print the sheet
.PrintOut copies:="1"

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 4
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

.PrintOut copies:="1"

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 4
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

.PrintOut copies:="1"

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 4
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End With
Next CopyNumber
End Sub

TIA,

Steve!

 

[Mike H]

Hi,

Insert an additional sheet which for this purpose I've call Countsh and
enter your number in A1.

This modified code will then read that number and use it and at the end
update the sheet with the new number. You will of course have to save the
workbook to make it work. Countsh can be hidden to prevent it becoming the
active sheet

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

For CopyNumber = 1 To CopiesCount
With ActiveSheet
'number in cell I1
.Range("I1").Value = Sheets("Countsh").Range("A1").Value +
CopyNumber
'Print the sheet
.PrintOut copies:="1"

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 4
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

.PrintOut copies:="1"

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 4
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

.PrintOut copies:="1"

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 4
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

End With
Next CopyNumber
Sheets("Countsh").Range("A1").Value = _
Sheets("Countsh").Range("A1").Value + CopiesCount
End Sub

Mike

 

[Joel]

I don't understand what you are looking for but there is a small problem with
your code. You should get the Now() each time you want hours, minutes, and
seconds because there is a small possiblity that you will grap the time just
as the hour changes. Try this instead

MyTime = Now()
newHour = Hour(MyTime)
newMinute = Minute(MyTime)
newSecond = Second(MyTime) + 4
waitTime = TimeSerial(newHour, newMinute, newSecond)

 

[Stephen]

Joel,

The whole purpose for this is to create a numbered purchase order form. My
initial reason for coding the time in that mannor was to place a buffer of a
few seconds between each print instance. I had run into problems using the
three-part paper and had misnumberings occuring.

 

[Stephen]

Mike,

Good show! exactly what I was looking for.

Thank you very much!

 

[Mike H]

Glad I could help

Mike,

Good show! exactly what I was looking for.

Thank you very much!