Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating cheque printing | Excel Worksheet Functions | |||
Creating and Printing Circles--they print out oblong | Excel Discussion (Misc queries) | |||
printing - creating new files on each page break | Excel Worksheet Functions | |||
Creating a counter that does not include hidden rows | Excel Discussion (Misc queries) | |||
Counter | Excel Programming |