Increment label printing excel VBA

Question

I am working on setting up increment printing for a series of labels printed from excel. there are four label numbers per sheet, with duplicate values ( total of 8 labels per page ). Ive attached a screen shot of the file.https://i.stack.imgur.com/jfSoz.png. The first label number, 26000, is the one that will be referenced in the script, the rest updated by a formula to add +1 to the number. I want the first number to be repopulated each time a sheet is printed for any given quantity of sheets. I've put together the script below, but each time i print it does not display the correct number. Instead of continuing the next series at the top,26005, it displays 5. Any help or suggestions would be appreciated.

Sub PrintNext5()

Dim xCount As Variant
Dim xScreen As Boolean
Dim I As Integer
On Error Resume Next

xCount = Application.InputBox("Please enter the number of copies you want to print:")
If TypeName(xCount) = "Boolean" Then Exit Sub
If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
    MsgBox "error entered, please enter again"
    
Else
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    For I = 1 To xCount
        ActiveSheet.Range("C3").Value = Range("C3" + 5)
        ActiveSheet.PrintOut
    Next
    
    Application.ScreenUpdating = xScreen
End If

End Sub

Miguel Angel Cortez Rodriguez Miguel Angel Cortez Rodriguez · Accepted Answer · 2021-04-20T22:14:37

as I can understand, your counter isnot counting correctly

try init your counter in the first value required, to (the fist plus the count of labels)

Check the code

Good Luck

Sub PrintNext5()

    Dim xCount As Variant
    Dim xScreen As Boolean
    Dim I As Integer
    On Error Resume Next

    xCount = Application.InputBox("Please enter the number of copies you want to print:")
    If TypeName(xCount) = "Boolean" Then Exit Sub
        If (xCount = "") Or (Not IsNumeric(xCount)) Or (xCount < 1) Then
            MsgBox "error entered, please enter again"

        Else
            xScreen = Application.ScreenUpdating
            Application.ScreenUpdating = False
            'your counter I in this case, should begin in 26000 and xCount should be xCount + 26000
            'you can try assign name to a cell in order to call it as name
            'begins = Sheets("otherSheet").Range("A1") <--Catch the first value from a cell
            For I = 26000 To xCount + 26000
            ActiveSheet.Range("C3").Value = I
            ActiveSheet.PrintOut
            I = I + 5 '<-- Your picture has 4 labels 1, 2, 3, 4, we are in 1 plus 4 = 5, next round begin in 5, 6, 7, 8,
            Next
        Application.ScreenUpdating = xScreen
    End If
End Sub

Increment label printing excel VBA

1 Answers