HELP! Generate 500 Invoices and Save each as a new .xls using VBA

[vaibs_ch]

I have two workbooks. One of the workbooks contains transactional data such as the date of the transaction, the name of the party, the value of the transaction, the transaction#, taxable amount, and other such details. Let's call this 'Sales_data'

The other workbook, 'Invoice,' has an invoicing format. Multiple columns from the 'Sales_Data' workbook are looked up in the 'Invoice' workbook, and I get a printable invoice. Typically this is an easy process.

Now because of the COVID situation, I have about 500 odd pending invoices that I want to generate. What I would have to do now is change the value of a cell to 1 (serial number of the invoice - this is different from transaction#) which would help me generate the first invoice. Save it as a new .xls file. Change the value of the serial #2, generate the second invoice. Save it as a new .xls file. Do this 500 times.

since I am a noob at VBA, I would really appreciate if someone could point me in the right direction, and help me generate the invoices. Thank you for your help.

Best,
Vaibhav

 

[DanteAmor]

Here's another way to pass values

Check the file name and add the add-in you need.

Sub Generate_Invoices()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set sh1 = ThisWorkbook.Sheets("APRIL20")
  Set sh2 = Workbooks("20200519_Inspection Receipt.xlsx").Sheets("Invoice")
  
  For Each c In sh1.Range("A201", sh1.Range("A" & Rows.Count).End(3))
    sh2.Range("I1").Value = c.Value
    sh2.Copy
    Range("A1:D37").Value = sh2.Range("A1:D37").Value
    ActiveWorkbook.SaveAs ThisWorkbook.path & "\" & "Invoice_" & c.Value, xlOpenXMLWorkbook
    ActiveWorkbook.Close False
  Next
End Sub

 

[vaibs_ch]

Hi Dante,

Thank you. This works and I think is more efficient than the code I used. Really thinking on picking up VBA skills now.

Best,
Vaibhav

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.