Auto Increment Cell Value By 2 After Each Print

[mir994stan]

I know its a boring question and i found many questions like this one. But im unable to edit code to suit my needs... I found code here with similar problem.

Auto increment A1 cell after each printing

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...

www.mrexcel.com

My situation:
I have a template on first page in Delivery sheet, range ("A1:K40"), so i need to print only first page of sheet. In cell "H10" i need to increment value by 2 after each print, and i also need to set number of pages for printing. For example, if i need 5 pages, on 1st page cell "H10" is 1, on 2nd is 3, on 3th is 5 and so on. Lets say that value of pages required will be in cell "M1". Value in cell "H10" is always whole number starting from 1. All other values in template are static. Maybe its a simple edit in code, but unfortunately i don't know how to edit... Many thanks in advance.

 

[Crystalyzer]

I set up this dummy worksheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1													5
2
3
4
5
6
7
8
9
10								1
11
12
13
14
15
16
17
18
19
20
Sheet1

And this code prints out the print range with the cell value H10 increasing by 2 each page up to five pages so that the value in H10 goes from 1 to 3 to 5 to 7 to 9 for a total of 5 pages as indicated in cell M1.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim totPgs As Long, pgNo As Long, i As Long
   
    Cancel = True
    Application.EnableEvents = False
   
    With Sheets("Delivery")
       
        For i = 1 To .Range("M1").Value
            If i = 1 Then
                .PrintOut copies:=1
            Else
                .Range("H10").Value = .Range("H10").Value + 2
                .PrintOut copies:=1
            End If
        Next i
        .Range("H10").Value = 1
    End With
   
    Application.EnableEvents = True
End Sub

 

[mir994stan]

It works great, thank you mate. U saved me with this. I have only one question, how can i run this from comand button in userform. I tried to record a macro to triger printing this sheet, but it can t work like that. Any ideas? Code i got from macro recorder:

ThisWorkbook.Worksheets("Delivery").PrintOut From:=1, To:=1, Copies:=1, Collate _
        :=True, IgnorePrintAreas:=False

 

[Crystalyzer]

You need the code below. The macro PrintCopies can be assigned to a command button on a user form. The Workbook_BeforePrint will fire when the PrintCopies macro is called and then it will take over the printing and incrementing.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim totPgs As Long, pgNo As Long, i As Long
    
    Application.EnableEvents = False
    
    With Sheets("Delivery")
        
        For i = 1 To .Range("M1").Value
            If i = 1 Then
                .PrintOut copies:=1
            Else
                .Range("H10").Value = .Range("H10").Value + 2
                .PrintOut copies:=1
            End If
        Next i
        .Range("H10").Value = 1
    End With
    
    Application.EnableEvents = True
    Cancel = True
End Sub

Sub PrintCopies()

ActiveWindow.SelectedSheets.PrintOut copies:=1

End Sub