Auto increment A1 cell after each printing

[Harry Geda]

Hello,

I would like to auto increment the cell A1 after every print.
Also if possible to have the luxury of entering a value in B1 for print quantity.
A1 should increment before printing the next print.
I am using the A1 as a serial number generator inside the form.

I am new to this. Only copied one macro before this.

Thank you,
Harry

 

[VoG]

In the Visual Basic Editor, in the Project window double click ThisWorkbook and paste in

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
With Sheets("Sheet1")
    .Range("A1").Value = .Range("A1").Value + 1
    .PrintOut copies:=.Range("B1").Value
End With
Application.EnableEvents = True
End Sub

Change Sheet1 to suit. The code will run automatically when you print (or print preview).

 

[Harry Geda]

Hello VoG,

I use A1=1 and B1=3 before printing.
The increment works for one increment. A1=2
Also printed all 3 pages with same number A1=2, B1=3
How can I change it so it will increment A1 after printing each page?

Started with A1=1 and B1=3
After printing A1=2 and B1=3

Regards,
Harry

 

[VoG]

Try

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Cancel = True
Application.EnableEvents = False
With Sheets("Sheet1")
    For i = 1 To .Range("B1").Value
        .Range("A1").Value = .Range("A1").Value + 1
        .PrintOut
    Next i
End With
Application.EnableEvents = True
End Sub

 

[Harry Geda]

Hello VoG,

I forgot to mention a couple of issues.
1- Thank you for taking my quetion.
2- This approach takes away all the usual controls that one may have before printing.
It jumps directly to print.
Anyway having control before jumping to printing?

Regards,
Harry

 

[VoG]

What sort of controls?

 

[Harry Geda]

The typical excel's print command will take you to a menu and will let you choose from choice of available printers.

With the Macro the printer direcly prints.
One has to have all of the settings done outside of the excel.

Regards,
Harry

 

[Harry Geda]

VoG,
The 2nd code works with multiple prints.
I still don't get a chance to select which printer I need to use.

Regards,
Harry

 

[VoG]

Try

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Cancel = True
Application.EnableEvents = False
Application.Dialogs(xlDialogPrinterSetup).Show
With Sheets("Sheet1")
    For i = 1 To .Range("B1").Value
        .Range("A1").Value = .Range("A1").Value + 1
        .PrintOut
    Next i
End With
Application.EnableEvents = True
End Sub

 

[Harry Geda]

VoG,

Thank you for beeing patient with me.

Now it works.
You allowed me to select printer but the original menu is still bypassed.
The standard printer dialog also allows you to do preview and then print.

Also, is it possible to not increment on preview?

Many thanks,
Harry