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]

Hi and welcome to MrExcel.

I help you with the macro, you could provide the following data:
- Name of the "workbooks contains transactional".
- Name of the sheet.
- in which column are the transaction #
- In which row do they start.

- Name of the book "Invoice".
- Name of the sheet.
- In which cell do you want to put the transaction #
- In which folder will the books be saved.
- The name of each book

Notes:
- The 2 books must be open.
- The macro goes in the book "workbooks contains transactional"

 

[vaibs_ch]

Hi Dante,

Thank you for your help with this. Please find below the required details.
Name of the workbook with transactional data: '20200519_DR-April.-2020 -'
Name of the sheet: APRIL20
Columns with serial # that is used to lookup the entire invoice: #A (INV.NO.)
Which Row do they start: #201

Name of the Invoice Workbook: 20200519_Inspection Receipt & GSTIN data
Name of the sheet: Invoice
Cell to put the serial #: $I$1
Folder for Books to be saved: Is a local drive folder which is the current path of these two books. The name of the folder is 'Office Files'
The name of each book: Since this is invoice data. It would be okay if we save each book as 'Invoice_Serial#' so 'Invoice_200', 'Invoice_201', and so on.

Also, I wanted to let you know that I am an intermediate level user at Excel. I could certainly read the code (may not be able to understand it completely.) I could definitely make minor changes to code if required.

Again, thank you so much for your help. I really appreciate it.

 

[DanteAmor]

Try this

Notes:
- The 2 books must be open.
- The macro goes in the book "20200519_DR-April.-2020 - "

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 & GSTIN data").Sheets("Invoice")
  
  For Each c In sh1.Range("A201", sh1.Range("A" & Rows.Count).End(3))
    sh2.Range("I1").Value = c.Value
    sh2.Copy
    ActiveWorkbook.SaveAs ThisWorkbook.path & "\" & "Invoice_" & c.Value, xlOpenXMLWorkbook
    ActiveWorkbook.Close False
  Next
End Sub

 

[vaibs_ch]

Hi Dante,

Thank you your response. I have pasted the code by inserting a module in the "20200519_DR-April.-2020 - " workbook. However, when I run this code (using the run button in the VBA window) I get an error message "Run-time error '9'." I am attaching the screenshot of the error message here for your reference. It would be super if you could please help me with this.

Best,
Vaibhav

 

[vaibs_ch]

I tried researching the error online and found that this error is typically generated when the code is unable to find a range/ object that it is referencing. I have ensured that both of the worksheets are open, and the names of the workbooks and sheets being referenced in the code match exactly. You will see that the name of the second workbook is not exactly what you typed in the code (does not include "& GSTIN Data".) This is since I tried debugging the code and shortening the name of the workbook to check if it would fix the error.

I am also attaching the screenshot of the VBA window for further information on this. I hope this helps.

 

[Mentor82]

Hi,
Try change sh2 line as below adding in workbooks name it’s extension whatever that is (.xlsx, .xlsm, or .xls etc)

Set sh2 = Workbooks("20200519_Inspection Receipt & GSTIN data.xlsx").Sheets("Invoice")

 

[vaibs_ch]

Hi Mentor82,

Thank you for your help. It works beautifully. I have two quick questions that I wanted to confirm.

1) Could I value paste all of my data in the range A1 through D37 so that nothing breaks when copying and pasting the Excels?
2) I have a user-defined function defined in the Module of my "Invoice" workbook that basically converts the currency in numbers to words. So for example 590 is basically written as Five Hundred and Ninety. This stops working when the worksheets as saved as "Invoice_Serial#" and throws a #Name? error. Is there a way to make this code global so that it works even when saving the sheet with a new name.

Thanks for all your help.

Best,
Vaibhav

 

[Mentor82]

Hi Mentor82,

Thank you for your help. It works beautifully. I have two quick questions that I wanted to confirm.

1) Could I value paste all of my data in the range A1 through D37 so that nothing breaks when copying and pasting the Excels?
2) I have a user-defined function defined in the Module of my "Invoice" workbook that basically converts the currency in numbers to words. So for example 590 is basically written as Five Hundred and Ninety. This stops working when the worksheets as saved as "Invoice_Serial#" and throws a #Name? error. Is there a way to make this code global so that it works even when saving the sheet with a new name.

Thanks for all your help.

Best,
Vaibhav

Hi,
Thanks for feedback. Glad we could help.

1. Regarding your first question here you have amended code. Adjust extension (xls, xlsx, or xlsm) in sh2 set syntax as I mentioned before. I set xlsx as previously because I do not have a clue which one relates to the workbook of yours.

Sub Generate_Invoices()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim c As Range
  Dim arr
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set sh1 = ThisWorkbook.Sheets("APRIL20") 
  Set sh2 = Workbooks("20200519_Inspection Receipt & GSTIN data.xlsx").Sheets("Invoice")

  arr=sht2.Range("A1:D37")
  For Each c In sh1.Range("A201", sh1.Range("A" & Rows.Count).End(3))
    sh2.Range("I1").Value = c.Value
    sh2.Copy
    ActiveWorkbook.Sheets(1).Range("A1:D37")=arr
    ActiveWorkbook.SaveAs ThisWorkbook.path & "\" & "Invoice_" & c.Value, xlOpenXMLWorkbook
    ActiveWorkbook.Close False
  Next
End Sub

2. Regarding your second question. Once the worksheet is copied to the new workbook the own-defined function will not work in such case for this reason: The module with function is not copied along with the worksheet to the new workbook and because it’s not there the error occurs. In order to make it work the whole module with the function would need to be copied the the new workbook would need to be saved not as xls or xlsx but xlsm (macro enabled). I can write the code for you but I would need to know the module name in which you have the user-defined function. Because in that case I’ll copy the whole module with all contents in pls check if everythin you want to have copied to the new workbook. If not, create a new module and cut the user -defined function, place to the newly created module and let me know the name of it.
What do you reckon?

 

[vaibs_ch]

Hi,

I was able to figure this out.
1) I amended the code as follows.

Range("A1:D37").Copy
    Range("A1:D37").PasteSpecial Paste:=xlPasteValues

2) For the second task, I found a thread on Mr.Excel that directed the Excel file containing the function to be saved as an Add-in file. (.xlam). I saved an imported add-in and ran the code shared by you. Worked like a charm.

Best,
Vaibhav