Voucher printing from Excel

**shahidr100** · 04-08-2017, 07:56 AM

Hello all,

I need help with multiple voucher printing from excel.

In sheet No.2 I have following data:
In Column A, I have put approx 2000 dates (dd-mm-yyyy).
I have voucher nos in Column B (approx 2000 voucher nos).
In column C, I have different description in each Cell.
IN column D, I have amount of voucher.
In column F, I have name of the person to whom I am paying. This could be few cells.
In column H, I have mentioned the reason for the payment. This also could be few cells as nature of work can be common.

In Sheet No.1 I have format of voucher (A5 size)

I want a vba or a method for printing to PDF/Or printer attached, all the above voucher in the format in Sheet No.1

Sample Data is as follows:

In Cell A1 -> 04 Apr 2017

In Cell B1 -> VCH0005

In Cell C1 -> "Being amount paid to "

In Cell D1 -> 500.00

In Cell F1 -> "Mr. John Doe"

In Cell H1 -> "for carrying goods from our office to some other office"

On the click of a button the above data will be printed in a particular
format which is designed in Sheet1 A5 size or A4 size.

Please help.
Thanks in advance.

**Sintek** · 04-08-2017, 09:11 AM

Hi Shahidr100

This code should sort you out...See attached sample workbook.Have not designed voucher but you will get the idea of how to populate the voucher and print

Option Explicit
Sub Voucher_Print()
Dim i As Integer
Dim lRow As Long
lRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lRow
    With Sheets("Sheet2")
        Cells(i, 1).Copy .Cells(2, 4) ' 2 , 4 REFERS TO D2
        Cells(i, 2).Copy .Cells(3, 4)
        Cells(i, 6).Copy .Cells(2, 2)
        Cells(i, 3).Copy .Cells(1, 2)
        Cells(i, 4).Copy .Cells(9, 4)
        Cells(i, 8).Copy .Cells(6, 2)
    End With
Sheet2.PrintPreview 'Change PrintPreview to PrintOut when you are ready to run macro
Sheet1.Activate
Next i
End Sub

**shahidr100** · 04-08-2017, 09:19 AM

Hello Sintek

Thanks for the help.

I'll work out on the code you have sent. I'll update if i succeed.
Thanks once again

**Sintek** · 04-08-2017, 09:19 AM

If you like you can design the voucher in your format and I will adapt the code to Auto populate and print

**AliGW** · 04-08-2017, 09:50 AM

You have to provide a link to where you asked the question on another forum. I will do it for you this time, as a courtesy, but in future you must do it yourself.

You HAVE posted the same query here: https://www.mrexcel.com/forum/excel-...ing-excel.html

**shahidr100** · 04-09-2017, 04:42 AM

Hello Sintek

I have altered the code little bit and it seems to be working. I have following three concerns right now.
1. As I am printing to PDF, each time I have to specify the file name. Isn't there any method where it prints all the vouchers in one file?
2. The narration can me more than one line long. Can we divide it into multiple lines through code? We can break the line by using " " as delimiter.
3. Figure to words code.

I am more concerned with point 1 above.

I have attached the xlsm file.

Thanks and regards

**Sintek** · 04-09-2017, 08:53 AM

Hi shakidr100

I am not able to recreate your scenario so cannot assist unfortunately. Why print to PDF?
Try this link and see if they can assist...not sure what pdf converter software you are using.
http://www.ozgrid.com/forum/showthread.php?t=47649

**shahidr100** · 04-09-2017, 01:00 PM

Hello sintek
Finally I have managed to do what I wanted to do.
I am attaching the excel file for other users.

All I need now is the code for figures to words and breaking the narration into multiple parts.
I am working on it. Will update if I achieve my goal.

Thanks to all of you for the help.
Regards

**Naresh1211** · 01-25-2019, 08:15 AM

Hello Sir,

In your Attachment there is no print button option if you can send again with that it will be help full. I am new to excel so i am not aware of the coding.

Thank You very much

**shahidr100** · 01-25-2019, 09:41 AM

Hello Naresh,

I am glad that you liked the post.

You will not find the print button on the worksheet.

There is a macro which is running in background so to print you have to do the following:

Open the worksheet and click press Alt+F8. A window opens. Make sure CreateVoucher Function is selected. Click on Run.

It will ask you to save/print the file.

I hope that helps.

Regards
Shahid.

**Naresh1211** · 01-25-2019, 12:53 PM

Hi Shahid,

Thank you very Much for your response.

I click press Alt+F8. As you said and followed the instructions its directly Printing the voucher its not requesting for any Save option.

If you figured the option for Number to word format it will be very helpful for me if you could upload the excel once again.

Thanks & Regards
Naresh

**shahidr100** · 01-31-2019, 06:23 AM

Hello Naresh,

You will have to install PDF printer in order to print to PDF.

If you do not have PDF printer, follow my Youtube channel to download, install Ghostscript and configure it to print to PDF for free.

Go to youtube and search with "how to print to pdf from windows 10 shahidr100". I guess you will reach my channel.

If you do not want to print to PDF at all then I'll send you the excel file after disabling the print option so that you can print to the printer of your choice.
I'll include number to word option also in that.

Let me know plz.

Thanks.

**Sintek** · 04-09-2017, 01:27 PM

Nice one shahidr100

You taught me something as well.

**shahidr100** · 04-10-2017, 01:03 AM

Hello Sintek,

I am glad that I was of help to you and people like me who will visit this post in future

Regards

**Naresh1211** · 01-25-2019, 07:54 AM

Excellent Post I will try with my requirements Thank You All.

**protonLeah** · 01-25-2019, 08:32 PM

Naresh1211,
Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**shahidr100** · 04-08-2017, 09:38 AM

I am sorry if I have done something wrong AliGW. I havent posted this question in the same forum at other place i guess.

Plz let me know how it can be solved.
Regards

Voucher printing from Excel

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Re: Voucher printing from Excel

Thread Information

Users Browsing this Thread

Similar Threads

Payment Voucher link to Import

Using Excel to Match Inventory to Voucher

salary voucher

Check Voucher Template

Printing of voucher

[SOLVED] gift voucher tracking

[SOLVED] How do you create voucher number in excel

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions