I found a VBA code on EctendOffice to increment a number in cell after each printing. Now I need to increment 4 cell values on same page after each printing. Example: when I set the number of printings to 50, Cell C27 should have a value 1/50, Cell M27 = 2/50, Cell C58 = 3/50, and cell M58 = 4/50. and the next page should be 5/50, 6/50, 7/50, 8/50 etc.

This is the code that I used to print only one label per page and to increment one cell value:

Sub IncrementPrint()
'updateby Extendoffice
    Dim xCount As Variant
    Dim xScreen As Boolean
    Dim I As Long
    On Error Resume Next
LInput:
    xCount = Application.InputBox("Please enter the number of copies you want to print:", "Kutools for Excel")
    If TypeName(xCount) = "Boolean" Then Exit Sub
    If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
        MsgBox "error entered, please enter again", vbInformation, "Kutools for Excel"
        GoTo LInput
    Else
        xScreen = Application.ScreenUpdating
        Application.ScreenUpdating = False
        For I = 1 To xCount
            ActiveSheet.Range("C27").Value = I & " / " & xCount
            ActiveSheet.PrintOut
        Next
        ActiveSheet.Range("C27").ClearContents
        Application.ScreenUpdating = xScreen
    End If
End Sub

CodePudding user response：

Print Copies of Single Worksheet With Increment

Option Explicit

Sub PrintWithIncrement()
         
    Const WORKSHEET_NAME As String = "Sheet1"
    Const RANGE_ADDRESS As String = "C27,M27,C58,M58"
    Const PROMPT As String = "Please enter the number of copies you want to print:"
    Const TITLE As String = "Print With Increment"
    Const DEFAULT_COPIES As Long = 1
    Const MAX_COPIES As Long = 100
    Const APPLY_TOTAL_LOGIC As Boolean = False
    
    Dim pCount As Variant
    Dim Msg As Long
    Dim IsInputValid As Boolean

    Do Until IsInputValid
        pCount = Application.InputBox(PROMPT, TITLE, DEFAULT_COPIES, , , , , 1)
        If VarType(pCount) = vbBoolean Then
            MsgBox "Dialog canceled.", vbExclamation, TITLE
            Exit Sub
        End If
        If Int(pCount) = pCount Then
            If pCount > 0 Then IsInputValid = True
        End If
        If IsInputValid Then
            If pCount > MAX_COPIES Then
                Msg = MsgBox("This will print " & pCount & " copies." _
                    & vbLf & vbLf & "Are you sure?", _
                    vbQuestion   vbYesNo   vbDefaultButton2, TITLE)
                If vbNo Then IsInputValid = False
            End If
        Else
            MsgBox "lnvalid entry: " & pCount & vbLf & vbLf _
                & "Try again.", vbExclamation, TITLE
        End If
    Loop
        
    Application.ScreenUpdating = False
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets(WORKSHEET_NAME)
    Dim rg As Range: Set rg = ws.Range(RANGE_ADDRESS)
    rg.NumberFormat = "@"
    Dim tCount As Long: tCount = pCount
    
    If APPLY_TOTAL_LOGIC Then tCount = tCount * rg.Cells.Count
    
    Dim cell As Range, p As Long, t As Long
    
    For p = 1 To pCount
        For Each cell In rg.Cells
            t = t   1
            cell.Value = t & "/" & tCount
            Debug.Print cell.Value ' Test with this first! Uncomment later!
            'ws.PrintOut ' Out-comment when done testing!
        Next cell
    Next p

    rg.ClearContents
    
    Application.ScreenUpdating = True

    MsgBox "Print job finished.", vbInformation, TITLE

End Sub

My Logic for 3 Copies (APPLY_TOTAL_LOGIC = TRUE)

1/12
2/12
3/12
4/12
5/12
6/12
7/12
8/12
9/12
10/12
11/12
12/12

Your Logic For 3 Copies (APPLY_TOTAL_LOGIC = FALSE)

1/3
2/3
3/3
4/3
5/3
6/3
7/3
8/3
9/3
10/3
11/3
12/3

CodePudding user response：

This is easily done with a loop and a variable for each of the 4 cells you want to have printed. After each iteration you have the variable value increase with four.

E.g. declare your variable

dim cell_m27 as integer 

Before your loop you set value to one

cell_m27 = 1

In your loop you increase with one each time:

cell_m27 = cell_m27   4

And replicate for the other three variables, but start them with 2, 3 and 4. Then they will increase with 4 for each loop until you reach your xCount and the loop is done.

CodePudding user response：

Sub IncrementPrint()

    Dim xCount As Variant, xScreen As Boolean, i As Long
    Dim rng As Range, bOK As Boolean, msg as string
    
    msg = "Please enter the number of copies you want to print:"
    Set rng = Range("C27,M27,C58,M58")
    rng.ClearContents
    rng.NumberFormat = "@"
    
LInput:

    xCount = Application.InputBox(msg)
    If TypeName(xCount) = "Boolean" Then Exit Sub

    bOK = False
    If IsNumeric(xCount) Then
        bOK = xCount > 0
    End If
    
    If bOK Then
        
        xScreen = Application.ScreenUpdating
        Application.ScreenUpdating = False
        i = 0
        Do While i <= xCount
            For Each c In rng.Cells
                i = i   1
                If i <= xCount Then
                    c.Value = i & "/" & xCount
                End If
            Next
            ActiveSheet.PrintOut
            rng.ClearContents
        Loop        
        Application.ScreenUpdating = xScreen

    Else
        MsgBox xCount & " not numeric,please enter again", vbInformation
        GoTo LInput
    End If
End Sub