2

I need to increment a value by 1 in K1 each time I print sheet number 1, 2, etc.

I used to have code that was saved and was working fine, but I lost it when I formatted the PC.

It used to ask me the number of pages needed to print and from which number to start.

When I use the below code, it works fine; but every time I print, the count starts from 1. I need to be able to print from whichever number I intend to.

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("A1").Value = " Company-00" & I
            ActiveSheet.PrintOut
        Next
        ActiveSheet.Range("A1").ClearContents
        Application.ScreenUpdating = xScreen
    End If
End Sub

Changing the I value to anything other than 1 will run the script, but not print any page.

Source file attached below https://easyupload.io/mx7c4l

Thank you LP Chip.

Struggling for a while with code errors. Can you please add the corresponding lines or share the code on a link?

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("K2").Value = " Sheet No:" & I
            ActiveSheet.PrintOut
        Next
        ActiveSheet.Range("K2").ClearContents
        Application.ScreenUpdating = xScreen
    For I = 0 + CountStart to xCount + CountStart - 1
    End If
End Sub

2
  • 1
    Doesn't this rule apply here (even if there is an external source)? Commented May 30 at 20:51
  • Peter, Noted, jpeg removed and code updated.
    – Shiv
    Commented Jun 2 at 18:51

1 Answer 1

3

The reason your script fails, is because you create a for loop that ran out of bounds. By adjusting 1 to a higher number than xCount is, you basically say: loop from 5 to 2 for example. In VBA, if the left value is bigger than the right, and you don't add a step -1, it will just skip the for loop. So the xCount value has to be increased also by the same amount.

To do this, you can make the following modification:

Add a new variable in your script: Something like CountStart, and either ask the user at script start, or fill it in manually.

Eg:

Dim CountStart as Integer = 1

Now adjust the for loop to keep the CountStart in mind.

For I = 0 + CountStart to xCount + CountStart - 1
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .