Add counter and print function

[KirstenNJ]

I have an excel sheet that I need to print a total of 1000 times but each time it prints I would like the value of one of the cells to count (from 000 to 999) and then print. In other words, each time it prints it would change that cell value by adding one and then printing. Sort of like printing 1000 tickets each with it's own unique number. Is there an easy way to do this?
Thanks,
Kirsten

 

[north19701]

Sub Print_Count()
n = 0
Do While n <= 999
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("a1") = n
    n = n + 1
Loop
End Sub

 

[jimboy]

Try this, change the range from A1 to your cell address;

Sub Macro9()
Dim mycounter As Long
mycounter = 1
Do Until mycounter = 1000
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

'change cell address below
Range("A1").Value = Range("A1").Value + 1
mycounter = mycounter + 1
Loop

End Sub

 

[amigos]

you can use that simple VBA code:

Sub PrintCounter()
Dim i As Integer

For i = 0 To 999

ActiveSheet.Cells(1, 1).Value = i
ActiveSheet.PrintOut

Next i

End Sub

It puts values from 0 to 999 in cell A1 and prints sheet every time

HTH

 

[just_jon]

Might be able to use something like *untested* --


<font face=Courier New><SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> Workbook_BeforePrint(Cancel <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Boolean</SPAN>)
    Range("A1") = Range("A1") + 1
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

which would be placed in the worksheet module to be printed, where A1 was the cell reference to be incremented.

 

[Joe4]

Here is a version that will enter the number as three digits, so it will maintain the leading zeroes:

Sub MyPrint()
        
    Dim i As Integer
    
'   Format cell as Text to maintain leading zeros
    Range("A1").NumberFormat = "@"
    
'   Print 1000 copies, each with different number
    For i = 0 To 999 Step 1
        Range("A1") = Format(i, "000")
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Next i
    
End Sub

 

[jimboy]

Sub Print_Count()
n = 0
Do While n <= 999
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Range("a1") = n
    n = n + 1
Loop
End Sub

What happens when the OP wants to print tickets from 1000 to 2000?

 

[north19701]

Good question...I just set it up to run from 0 to 999 as it had stated in the original post.

 

[KirstenNJ]

Jmiskey,
I would like to use your code since you kept the leading zeros which I need to do, however, I get a compile error syntax error message and it highlights the copies:=1 line. Any ideas what is wrong with that part?
Thanks,
Kirsten

Here is a version that will enter the number as three digits, so it will maintain the leading zeroes:

Sub MyPrint()
        
    Dim i As Integer
    
'   Format cell as Text to maintain leading zeros
    Range("A1").NumberFormat = "@"
    
'   Print 1000 copies, each with different number
    For i = 0 To 999 Step 1
        Range("A1") = Format(i, "000")
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Next i
    
End Sub