printing numbers on tickets or vouchers

  • Hi


    I have built a voucher in XL. I have prepared three of the same voucher on one page to save paper. I want to be able to print numbers on each voucher. I want to print, let say, 500 vouchers and I want the number to be printed in the same cell on each voucher. Also, when I need more vouchers, to start from 501, I want to be able to enter the number 501 and print however many vouchers I need. I do not know VBA, but if it means I have to copy the statement into an existing macro I'll survive.


    Thanks for prompt replies and hope that it's not too complicated!

  • Hi NelsonM
    I think this code might do the trick.
    Add this to a code module in your workbook. You will need to define the voucher no. on your template as a named range. and sub it in where you see Invoice_no.
    Graeme
    [vba]
    Sub Auto_Increment()
    Dim x As Integer
    Dim Inv As Integer


    x = InputBox(prompt:="Enter the number of your last Invoice you want to print. ")
    Inv = [Invoice_no].Value
    If [Invoice_no] = "" Then Exit Sub

    Do Until i > x
    Inv = [Invoice_no].Value
    i = Inv
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    [Invoice_no].Value = Inv + 1
    i = i + 1
    Loop

    If i > x Then
    [Invoice_no].Value = Inv
    End If
    End Sub
    [/vba]

  • Hi Imbuzi


    I have checked your code and it works fine on one item per page. NelsonM requires 3 per page. I have played about and set up a dummy page with 3 vouchers, then with the addition of a couple of formulae and amendment of your code I think it now works as required.
    Hope you don't mind a little interference.:barf: :cheers:
    [vba]
    Sub Auto_Increment()
    Dim x As Integer
    Dim Inv As Integer


    x = InputBox(prompt:="Enter the number of your last Voucher you want to print. ")
    Inv = [Num].Value
    If [Num] = "" Then Exit Sub

    Do Until i > x
    Inv = [Num].Value
    i = Inv
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    [Num].Value = Inv + 3
    i = i + 1
    Loop

    If i > x Then
    [Num].Value = Inv
    End If
    End Sub
    [/vba]
    NelsonM please note that you must enter in the message box the last voucher number such as 9, which will actually finish on 11 because it is working on 3 per page. It might be possible to change the named range to the 3rd number and change it that way.

  • Hi


    Thanks a lot for your response. I'm afraid that I will only be able to check it out on the weekend. But, I really appreciate your response.


    See you later


    Brian


    :spin:

  • Hi! I'm dealing with a similar challenge. Only, I would like to print in batches of 10 sheets, where I can cut the sheet in 3 or 4 vouchers stacks of 10 and then put one under the other. So the top voucher on the first sheet would be 001, the middle one would be 011, and the bottom voucher would be 021. The second sheet 002, 011, 022, etc.:)

  • Hi Rene


    Welcome to the Forum.


    The example only needs a little altering to produce your results. I have changed the formulae and code slightly.



    Click the button and enter 30 in the message box, the macro will print 10 sheets numbered 1,11,21 then 2,12,22 and so on. I think this is what you needed.

  • Thanks Roz!:))


    I'll experiment further with it later tonight.
    It'll work faster then what I had tooled together before (see attached...)

  • Re: printing numbers on tickets or vouchers


    Hi
    I don't understand enough of this to get how you import, insert code into a macro. How you connect macro module to certificates in excel speadsheet. How you get certificate image to be behind the spreadsheet generated numbers field so that the field shows on top of image when you print out certificates.
    thanks
    mike

  • Re: printing numbers on tickets or vouchers


    mdsmike welcome to ozgrid


    To enter the code into a workbook, preas Alt | F11 to open the VBE Editor. Then whilst in the VB Editor use the Insert Menu & choose Module. Then Copy & Paste the code example.


    I am not sure what you require from your question - the code in this thread is for printing tickets - several per printed sheet, each ticket to have a unique number. There is no mention of images in the code. Please explain what you are trying to do.

  • Re: printing numbers on tickets or vouchers


    i am trying to use word or excel to print multiple gift certificates (images-pictures of gift certificates) per page. 4-6 per page would be great. And on each gift certificate have a consecutive number that i can choose.
    I don' know how to print the consecutive numbers on gift certificates on multiple pages using the coding in the above posts.
    Thanks
    Mike

  • Re: printing numbers on tickets or vouchers


    i am trying to use word or excel to print multiple gift certificates (images-pictures of gift certificates) per page. 4-6 per page would be great. And on each gift certificate have a consecutive number that i can choose.
    I don' know how to print the consecutive numbers on gift certificates on multiple pages using the coding in the above posts.
    Thanks
    Mike

  • Re: printing numbers on tickets or vouchers


    am trying to use word or excel to print multiple gift certificates (images-pictures of gift certificates) per page. 4-6 per page would be great. And on each gift certificate have a consecutive number that i can choose.
    I don' know how to print the consecutive numbers on gift certificates on multiple pages using the coding in the above posts.
    Thanks
    Mike
    How did you layout gift certificates? In word or excel?

  • Re: printing numbers on tickets or vouchers


    Here's the original workbook that was attached.

  • Re: printing numbers on tickets or vouchers


    Quote from royUK

    Here's the original workbook that was attached.


    Here is the modified workbook with an image (gift certificate) which i would like the auto generated consecutive number field to be on top of.
    and I only want to print the gift certificates not the extra yellow box text on the side(the instructions tabs that are yellow colored with the print batch button in the center of yellow tabs)
    thanks
    mike
    any help is greatly greatly appreciated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!