While working with large Microsoft Excel, sometimes we need to create an invoice and save the invoice into PDF format. We can easily do that by using the VBA Macros. Applying a VBA code to create an invoice and save it into PDF format is an easy task. Today, in this article, we’ll learn two quick and suitable steps to create an invoice and save PDF format by applying the VBA code in Excel effectively with appropriate illustrations.
Create Invoice and Save PDF Format with Excel VBA (Quick View)
Private Sub Create_invoice_save_pdf()
'for invoice number
Sheet2.Range("C5").Value = Sheet2.Range("C5").Value + 1
'for pdf file save
Sheet1.Range("B2:E20").ExportAsFixedFormat xlTypePDF, Filename:=_"C:\Invoice\" & Sheet.Range("C5").Value, openafterpublish:=True
'for clear invoice
Sheet2.Range("B12:E15").ClearContents
Sheet2.Range("E17:E19").ClearContents
End Sub
How to Create Invoice and Save PDF Format with Excel VBA: 2 Easy Steps
Let’s say, we have a dataset that contains information about the invoice of the Star Restaurant. After that, we will save it into PDF format by using the VBA macros. We will apply the SUM function to create an invoice of the Star Restaurant. Here’s an overview of the dataset for today’s task.
Step 1: Use SUM Function to Create Invoice and Save PDF Format
First of all, we will create an invoice of the Star restaurant. Let’s follow the instructions below to learn!
- First, select cell E12.
- After selecting cell E12, write down the below mathematical formula in that cell.
=C12*D12
- Simply ENTER on your keyboard, and you will get $750.00 as the return of the mathematical formula.
- Now, autoFill the SUM function in the rest of the cells in column E.
- After that, select cell E17 and type the below SUM function in that cell. The SUM function is,
=SUM(E12:15)
- Further, again, press ENTER on your keyboard, and you will get $1670.00 as the return of the SUM function.
- Now, we will give a 5% discount to the customer depending on the sale value. To calculate the discounted price, select cell E19, and type the below formula in that cell. The mathematical formula is,
=E17*95%
- Again, press ENTER on your keyboard, and you will get $1586.50 as the return of the mathematical formula which has been given in the below screenshot.
Read More: Excel Macro to Save as PDF with Filename from Cell Value
Step 2: Save PDF Format with Excel VBA
Now we will apply the VBA code to save the PDF file of the invoice that has been created in step 1. This is an easy and time-saving task. Let’s follow the instructions below to save the PDF format of the invoice!
- First of all, open a Module, to do that, firstly, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Invoice will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
- Hence, the Invoice module pops up. In the Invoice module, write down the below VBA
Private Sub Create_invoice_save_pdf()
'for invoice number
Sheet2.Range("C5").Value = Sheet2.Range("C5").Value + 1
'for pdf file save
Sheet1.Range("B2:E20").ExportAsFixedFormat xlTypePDF, Filename:=_"C:\Invoice\" & Sheet.Range("C5").Value, openafterpublish:=True
'for clear invoice
Sheet2.Range("B12:E15").ClearContents
Sheet2.Range("E17:E19").ClearContents
End Sub
- Hence, run the VBA To do that, go to,
Run → Run Sub/UserForm
- After running the VBA code, you will be able to save PDF format of the invoice of the Star Restaurant which has been given in the below screenshot.
Read More: Excel Macro: Save as PDF with Date in Filename
Things to Remember
👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.
👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,
File → Option → Customize Ribbon
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
I hope all of the suitable methods mentioned above to create an invoice and save the invoice into PDF format with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.