Print sequential numbers

[Macdougall91]

Good afternoon,
I used to have an employee working for me that new everything about excel and he set up an excel sheet for us that we could print sequential numbers in K1 by pressing qtrl Q

Once qtrl Q was pressed a dialog box would pop up asking how many copies to print and we could choose how many copies and it would print that many copies with a new number in the K1 area.

Unfortunately the excel that he built for us was lost and he has moved onto bigger and better things.

We have recreated the excel but cannot figure out how to get qtrl Q to print an C amount of copies with a new number in K1.

Hoping someone here can help with that, we need K1 to start at 100000 and then count up by one from there.

Once we hit qtrl Q and entered the number of copies we wanted excel would print that number and then the ending number would be the new number in the K1 box so that next time excel was opened we knew where to start printing from again.

 

[Micron]

I can't help but think it would be a lot easier to work from a command button on a sheet rather than trying to capture 6 key presses and determine that they were done in a specific order; especially if there is a mix of upper and lower case. That or some unused keyboard combination (e.g. ctrl+alt+some other key). If that other key could be a number from 1 to 9, you have just entered the number of copies. Or will the number sometimes be higher than 9? You haven't said what is being printed.

 

[Macdougall91]

I can't help but think it would be a lot easier to work from a command button on a sheet rather than trying to capture 6 key presses and determine that they were done in a specific order; especially if there is a mix of upper and lower case. That or some unused keyboard combination (e.g. ctrl+alt+some other key). If that other key could be a number from 1 to 9, you have just entered the number of copies. Or will the number sometimes be higher than 9? You haven't said what is being printed.

Thanks for the reply, and sorry my question wasnt worded the simplest way.

What i was trying to do was print multiple hand written invoice papers with seperate invoice numbers on each. The invoice number is located in box K1 in the excel sheet.
I wanted the invoice number to add 1 after each print but wanted to be able to print 100 copies or 200 copies or however many i wanted every time i printed.
We used keyboard short cut of Qtrl Q to run the macro.

I was needing a macro to do this as well as set up the short cut to run the macro by pressing qtrl Q

With that said i figured it out and below is what i used for the macro:

Sub PrintNumberedCopies()
    Dim iCopies As Integer
    Dim J As Integer
    Dim r As Range

    ' Specify the cell to modify
    Set r = Range("k1")

    ' Get the number of copies.
    iCopies = Val(InputBox("Number of copies to print:"))

    If iCopies > 0 Then
        ' Loop iCopies times, printing once per loop
        For J = 1 To iCopies
            ActiveSheet.PrintOut
            r.Value = r.Value + 1
        Next J
    End If
End Sub

Below is how i set up the keyboard shortcut to run the macro
1. Once returned to the excel press Alt + F8
2. Click on options
3. Set short cut key as Q


Hopefully this will help a beginner out if they are needing to accomplish the same thing.

 

[Micron]

Well I did learn something about Application.OnKey in the process of playing around so not a total loss!
Simply typing a capital Q in that sheet will fire your code?

 

[Macdougall91]

Well I did learn something about Application.OnKey in the process of playing around so not a total loss!
Simply typing a capital Q in that sheet will fire your code?

Not just Q you need to use qtrl+q and then it will run the macro

 

[Micron]

I'm confused but I guess it doesn't matter since you have it working. In your original post I thought you might have meant ctrl key plus Q but you wrote "qtrl Q" more than once so I took to mean the key sequence had to be q then t then r then l then a space then Q. That's why I mentioned a button instead of 6 key presses to run code. Your last post now makes me think it is the control key (ctrl) and a capital Q after all.

 

[Macdougall91]

I'm confused but I guess it doesn't matter since you have it working. In your original post I thought you might have meant ctrl key plus Q but you wrote "qtrl Q" more than once so I took to mean the key sequence had to be q then t then r then l then a space then Q. That's why I mentioned a button instead of 6 key presses to run code. Your last post now makes me think it is the control key (ctrl) and a capital Q after all.

It’s is ctrl key plus q sorry not sure why I kept typing qtrl, had Q on my mind I guess.

Yes either way the macro works and no I’m able to run it by pressing ctrl plus q and then enter how many copies I would like to print.

 

[Peter_SSs]

@Macdougall91
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.