Add 1 Number when it prints not a loop

[casamalm]

I want to print a document and everytime that it prints, I just want it to add a counter on a cell box. This is the code that I found here prints 999 pages, I just need it to print once and keep that number till next time we print. Can any one help so it prints once?

Sub MyPrint()

Dim i As Integer

' Format cell as Text to maintain leading zeros
Range("L4").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

 

[CraigM]

Something like:

Sub MyPrint()
With ActiveSheet
.Range("A1") = .Range("A1") + 1
.PrintOut Copies:=1, Collate:=True
End With
End Sub

This increases A1 by one, and prints the active sheet. You may want to be a bit more precise by writing the actual sheet, rather than using the activesheet.

 

[Derek]

Hi there

You might like to experiment with code like this:

In the VB Editor select ThisWorkbook from the Project window and paste in this code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name <> "Mysheet" Then Exit Sub
ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value + 1
End Sub

Change "Mysheet" to the name of the worksheet you will be printing and change "A1" (both instances) to the cell you wish to hold the number.


regards
Derek

 

[CraigM]

A quick warning about the BeforePrint event. It is "like is says on the tin", Before print. This means that if a user clicks File>Print, and then Cancel your code still runs, even though the sheet was not actually printed. It may not be an issue, but it pays to be aware of that.

 

[Derek]

Good point CraigM. On second thoughts it probably wouldn't increment the number when printing several pages at once either. Your solution is the way to go.

regards
Derek