bman84
10-05-2014, 04:32 PM
Hey there, hoping you can help me piece together the code to create a simple macro. I have an Excel spreadsheet that acts as a simple statement generator by populating a number of cells based on a VLOOKUP against a master list of account numbers.
The user enters the 4-digit account number, and the VLOOKUP formulas on a separate tab of the workbook bring back the required values (ie. Customer Name, Address, Balance, etc) from the master list.
Rather than have the user spend time entering an account number, and manually saving a PDF, I'm hoping to create a macro that does those steps for them. Desired process would look like this:
1) User pastes list of account numbers in COLUMN L on 'Entry' sheet
2) User enters a desired file path in cell A5 (location where PDF's will be saved)
3) User clicks a button to start the macro
4) Macro reads first account number in COLUMN L and writes it to cell H7
5) Macro moves to separate sheet called 'Renewal Letter' and saves the sheet as a PDF, with the same filename as the text in cell K6
6) Macro moves back to 'Entry' and writes the next account number from COLUMN L to cell H7
....and the loop of steps 5) and 6) continues until the entire list in COLUMN L has been completed
Thanks in advance for any help!
The user enters the 4-digit account number, and the VLOOKUP formulas on a separate tab of the workbook bring back the required values (ie. Customer Name, Address, Balance, etc) from the master list.
Rather than have the user spend time entering an account number, and manually saving a PDF, I'm hoping to create a macro that does those steps for them. Desired process would look like this:
1) User pastes list of account numbers in COLUMN L on 'Entry' sheet
2) User enters a desired file path in cell A5 (location where PDF's will be saved)
3) User clicks a button to start the macro
4) Macro reads first account number in COLUMN L and writes it to cell H7
5) Macro moves to separate sheet called 'Renewal Letter' and saves the sheet as a PDF, with the same filename as the text in cell K6
6) Macro moves back to 'Entry' and writes the next account number from COLUMN L to cell H7
....and the loop of steps 5) and 6) continues until the entire list in COLUMN L has been completed
Thanks in advance for any help!