Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto increment & print three copies
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto increment & print three copies
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 "Stephen" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto increment & print three copies
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" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto increment & print three copies
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. "Joel" wrote: 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" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto increment & print three copies
Mike,
Good show! exactly what I was looking for. Thank you very much! "Mike H" wrote: 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 "Stephen" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto increment & print three copies
Glad I could help
"Stephen" wrote: Mike, Good show! exactly what I was looking for. Thank you very much! "Mike H" wrote: 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 "Stephen" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of copies to print keep defaulting to 12 copies why? | Excel Worksheet Functions | |||
auto increment | Excel Discussion (Misc queries) | |||
ask for one print and it gives 6 copies | Excel Discussion (Misc queries) | |||
DP Auto increment | Excel Worksheet Functions | |||
How do I auto increment cell value on print in Excel | Excel Worksheet Functions |