Hello,

I'm sure this may have been previously asked but I am exceedingly frustrated by my internet searches, trying to find a macro that will work for me. I'm relatively new to writing macros (I did it years ago, but only simple ones and it's been a long time since I've done any). Any help would be greatly appreciated! Here's my set up and what I'm trying to do:

Working in Excel 2010, one workbook, with 3 worksheets. I've got contact data on one worksheet (we'll call it the "Data" worksheet), accounting figures, calculations, etc. on the 2nd worksheet (we'll call this one accounting), and in the last worksheet, I've got an invoice template, which pulls it all together (called invoice).

Within this final "invoice template", I have a pull down list, that lists all of the customers (there are 9 customers currently, with room for an additional 4, and the list populates from information on the data worksheet). With the Vlookups I have built into this template, when I select one of the customers from the list, it auto populates and calculates all of the required information into the invoice from the data and accounting worksheets.

Here's what I'm trying to do with the macro: I'd like to make it so that if I push a button, it will automatically produce the invoices (preferably in PDF) for all of the current customers, while saving it to a specific folder in a shared drive, and uses a specific cell in each invoice, to name each file (I have a cell that does a vlookup from others, to produce a unique invoice number).

**I don't want to make this too complicated, so what I've described above is what I'd like the macro to do, at a minimum. If anyone's able to easily help me with that request and could help me make this macro what I really want, here are some additional things I'd like it to be able to handle (and I don't know if this is possible).

-As I previously indicated, there are currently 9 customers, with room for an additional 4 (the way I have the lookups, etc, built, it'll only produce text in the drop down list and produce the figures from the v-lookups, if all of the customer data is entered first). That being said, I'd prefer to not have to edit the macro down the line if we do add customers, but at the same time, I don't want it producing blank invoices from the 4 placeholders I have in the worksheet right now. In summary, even though there are 13 spots in the dropdown list, I need it to only produce the first 9 invoices, and only produce any additional invoices, if we add customers. I hope this doesn't make it more confusing, but I'm happy to provide additional clarification, if needed.

Finally, in addition to the worksheet I have labeled as "invoice", there are also "invoice-year2" and "invoice-year3" worksheets. These bills will be sent out annually, and if possible, I'd like to be able to program the macro so that when the time comes, year 2 and year 3 can be just as easily run. I'm assuming I can just record a macro for each worksheet for the next couple years, but ideally, I'd like to be able to just select what year I want to run, and click a button or shortcut, and it'l run the year I've asked.

Thanks in advance for any help!


Jenny