Help with automated invoice generator

Good day everybody!

I hope to find everyone well.

I am trying to put up an automated invoice generator.

In few words, what I am trying to achieve is:

An invoice generator that works with VBA and macros, so that:

1. It can give these invoices difference invoice numbers and store them in a separate excel spreadsheet, with name of client, today's date and invoice number
2. It works with dependant lists, meaning that if I select Variable X in cell A1, then I will be able to select only variable x or y or s from the list, and viceversa.
3. It can generate up to 200 invoice at time and save them to pdf all at once (I am guessing I will have to record the macro to do so)

I have attached a screenshot of what my invoice generator looks like right now.

More in detail: I have two spreadsheets, one with the invoice generator (screenshot attached) and one with a table with different variables in sheet1, from which I am looking up data to put into my invoice generator.
As you can see from screenshot, I just have one simple calculation in the invoice generator, which is management fee*Q1/2/3. The mgmt fee and Q1/2/3 values are looked up from the table with different variables in sheet 1

My questions are as follows:
Issue 1) How can I generate invoice numbers, that are stored in a different spreadsheet and include name of client, date and amount.
Issue 2)How can I make it so that if I select a list of variables (5, precisely) in the cell next to "Variable 1", then I get a different set of variables in "account name". Account names and "variable 1" are looked up from a different spread sheet and each one of " variable 1 " has 5 different spread sheets with different account names.
Issue 3) What kind of code do I have to put in the generate invoice / export to PDF button so to have the invoice exported to excel? ( I just put the button there; no code is linked to it )
How can I automate the invoice generator, meaning I don't have to select each variable/account name and manually press " generate invoice " for each one of the invoices I want to generate? I am guessing it's a macro? Or is there a better way to do it?

Thank you in advance for your help. I will be more than happy to share the spread sheet once I have gotten everything to work ( with your help, hopefully ) , so that everybody can use it. I am not sharing the excel spreadsheet as it has a lot of sensitive data in it, but if the above is not clear, I can create a dummy one.

Cheers!
Michael

Hi, Michael
I can’t promise I can help you, because actually I'm a beginner in excel vba, but I like learning something new.
Hope some expert here can find the solution of your problem so I can learn from it too.

but if the above is not clear, I can create a dummy one.

Yes, off course it's better if you upload it.
Can you explain your step one by one when you create an invoice? So maybe I can see which part that can be done easier by adding formula somewhere or using macro.

Hi Akuini,

Thank you for your reply.

I have attached a dummy of what I have created. I managed to get the save to pdf macro right, and also managed to do it in a way so that every time I save to PDF, it increments the invoice number by 1. Feel free to use it!

Now I am stuck with dependent variables, can't get this one right.

I attached the dummy sheet so you can see what I mean. In the invoice generator, i have two cells one called Registered office and one called company names. I would like to have dependant variables set in a way that when I select "Barba" as reg office, it shows only Barba's companies. Same for the other registered offices (pelle, naso, ecc..)

Problem is I have different sheets for the variables. I tried creating one sheet with only registered office and correspondent company names (that's the "reg office details" sheet) but I can't seem to get it right. I also have a vlookup set so that when I select a certain registered office name it automatically displays the right address.

Your help would be greatly appreciated! If you have any other suggestion I am also willing to restructure the sheet a bit.

My next challenge will be creating a macro that generates an invoice for each registered office and each company name.... any suggestion?

Thank you for your help!