VBA Macro help please

[JohnRob83]

hi all,

please can someone help me?

i have a file called InvoiceTemplate.xlsx which is an invoice. i have a separate file called InvoiceData.xlsx which contains multiple rows of data. id like a macro that would save the Invoice Template multiple times(50 times) loop until finished, update certain cells and rename the file based on the data from InvoiceData.xlsx

i have spent many hours trying to find a solution with no luck.

InvoiceData.xlsx contains as follows:

• Cell A4 - File name - to be used to save the template file as
• Cell B4 - Invoice number
• Cell C4 - Invoice date
• Cell D4 - Net amount

InvoiceTemplate.xlsx contains

• Cell H9 - Invoice number
• Cell H8 - Invoice date
• Cell G5 - Net amount

both files are on a server but for testing you can assume they are both saved in C:\manual invoices\test

any help would be greatly appreciated, i have used macros before but for formatting and i am really struggling with this.

thanks
JohnRob83

 

[NdNoviceHlp]

You are welcome. I trialed it again and it works as before. If you have data in sheet1 A4:D & Lastrow of your Invoicedata.xlsm file then it should work. Good luck. Dave

 

[JohnRob83]

just out of interest, are you using office 365?

 

[NdNoviceHlp]

That was tested with Office 2016 on Windows 10. I just trialed on Office 2021 on Windows 11.... works faster. Dave

 

[JohnRob83]

Hi Dave,

Fresh eyes this morning worked a treat! i had 2 macros called the same thing, one in personal workbook and the other in the Invoice data workbook. when i try to run it ive chosen the personal workbook one, hence why it wasnt bringing any data back!

thank you for all the help, it really is appreciated, you are a legend!!

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Have a nice day. Dave