How to create invoices in excel VBA?

Please see the following code for creating invoices with VBA.

How my excel workbook looks like

In order to generate the invoices for my different suppliers I have set up two sheets for this. First one being my supplier list and second one being my invoices template which showcases how my invoices will look like. Please click on the images below to view my worksheets.

Code

Sub GenerateMyInvoices()
 
 Application.ScreenUpdating = False 'Turns off screenupdating
 Supplier_Sheet.Activate
 For Each Cell In Range("A3:A10") 'my range that I will look in
  If Cell <> "" Then
 
 
 '******************************************** Moving data to the invoice template *******************************
  'Moving data to the invoice template sheet, and saving in excel and pdf formats.
 
  'Moving Supplier Name
  Invoice_Sheet.Range("B12") = Cell
  Invoice_Sheet.Range("D12") = Cell
 
  'Alternatively we can write the following to move the supplier name for example.
  'Invoice_Sheet.Range("B12,D12") = Cell
 
  'The benefit of this is that we are only doing one write action, thus speeding up the script.
  'Also, this is practical as we only have to change one line of code instead of two, should something change.
 
  'Moving Contact Name
  Invoice_Sheet.Range("B13,D13") = Cell.Offset(0, 1)
 
  'Moving Address
  Invoice_Sheet.Range("B14,D14") = Cell.Offset(0, 2)
 
  'Moving amount
  Invoice_Sheet.Range("F18") = Cell.Offset(0, 3)
 
 
  'Now we have moved all the necessary data in order to save each invoice.
 
  'Where we want to save it. please complete this with your own folderpath.
  Dim strfolderpath As String
  strfolderpath = Range("H2")
 
  'Our filename
  Dim FileName As String
  FileName = Cell
 
  'Complete filepath and name
  Dim strPath As String
  strPath = strfolderpath & "\" & FileName
 
  'If we had a specification sheet for terms and conditions, _
  or just multiple invoice sheets that are located in separate worksheets _
  we could use the following syntax to activate the sheets and get a 2-paged invoice.
 
 
  'Please note that it could be good to amend the print layout in the Invoice template so that all your data _
  is included and that you get it in a nice format. You can amend the print area under tab _
  "View/Page Break Preview".
 
 
 '******************************************** Saves 1 page in either .xlsx or pdf*******************************
  'saves as .pdf format.
  Invoice_Sheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
  'saves as .xlsx format
  Invoice_Sheet.Copy
 
  With ActiveWorkbook
  .SaveAs FileName:=strPath & ".xlsx", FileFormat:=xlOpenXMLWorkbook
  .Close SaveChanges:=False
  End With
 
 
 '******************************************** Saves multiple pages in either .xlsx or pdf***********************
 
  'saves as .pdf
  Sheets(Array(Supplier_Sheet.Name, Invoice_Sheet.Name)).Select
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strPath, Quality:=xlQualityStandard, _
  IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
  'saves as .xlsx format
  Sheets(Array(Supplier_Sheet.Name, Invoice_Sheet.Name)).Copy
 
  With ActiveWorkbook
  .SaveAs FileName:=strPath & ".xlsx", FileFormat:=xlOpenXMLWorkbook
  .Close SaveChanges:=False
  End With
 
 '******************************************** End of saving options*********************************************
 
 
  End If
 Next Cell
 
 
 Application.ScreenUpdating = True 'Turns on screenupdating again
 ​
 MsgBox ("Done")
 
 
 End Sub

Download Excel Workbook here.

Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.