Scrubbrush
New Member
- Joined
- Jan 2, 2017
- Messages
- 3
Hi -
I'm a veteran Excel guy but a newby to VBA and I know this should be simple. I'm pretty good a googling VBA stuff and figuring out macros but I'm stuck on a simple one. I've checked all the Mr. Excel forms i could find on related subjets.
Background: I have a sheet with client information. Each row has a different client and each column has a datapoint about that client. There are about 100 rows and 16 columns. The client data starts on row 9, column D.
Goal: I need Excel to: 1) open a pre-formatted (existing) PDF form, 2) fill in the client name and other info from the "first row", 3) "save-as" the PDF into a specified folder, 4) close the PDF, 5) open a new blank PDF form, 6) populate the client info from the next line, 7) repeat this process until the "last row". I have gotten all of this to work fine, except...
Issue I need help with: I would like to be able to define which row (of the 100 client-rows) is the "first row" and the "last row". I would like to able to type the number for the "first row" in cell E6 and the number of the "last row" in E7 (on the same page as the client data, above the table containing client data) so I can choose to fill out forms for, say, client-rows 50 through 60 by typing 50 into E6 and 60 into E7.
Current status: I have everything working. For example, I can create the PDFs for the first four clients using this code:
For ClientRow = 9 To 12
I can change range of client-data-rows that will fill the PDF by changing the numbers in the above code. It does everything I mentioned in "Goals" above.
All I need help with is code that will replace the "9" and "12" above with the value found in cell E6 ("first row") and E7 ("last row")
I'd like to call the "first row" "FirstClient" and the"last row" "LastClient" but i can never quite figure out the "dim" and "set to" and "FirstClient = " stuff...
I hope that all makes sense. It's a long way of asking a simple question.
I'm a veteran Excel guy but a newby to VBA and I know this should be simple. I'm pretty good a googling VBA stuff and figuring out macros but I'm stuck on a simple one. I've checked all the Mr. Excel forms i could find on related subjets.
Background: I have a sheet with client information. Each row has a different client and each column has a datapoint about that client. There are about 100 rows and 16 columns. The client data starts on row 9, column D.
Goal: I need Excel to: 1) open a pre-formatted (existing) PDF form, 2) fill in the client name and other info from the "first row", 3) "save-as" the PDF into a specified folder, 4) close the PDF, 5) open a new blank PDF form, 6) populate the client info from the next line, 7) repeat this process until the "last row". I have gotten all of this to work fine, except...
Issue I need help with: I would like to be able to define which row (of the 100 client-rows) is the "first row" and the "last row". I would like to able to type the number for the "first row" in cell E6 and the number of the "last row" in E7 (on the same page as the client data, above the table containing client data) so I can choose to fill out forms for, say, client-rows 50 through 60 by typing 50 into E6 and 60 into E7.
Current status: I have everything working. For example, I can create the PDFs for the first four clients using this code:
For ClientRow = 9 To 12
I can change range of client-data-rows that will fill the PDF by changing the numbers in the above code. It does everything I mentioned in "Goals" above.
All I need help with is code that will replace the "9" and "12" above with the value found in cell E6 ("first row") and E7 ("last row")
I'd like to call the "first row" "FirstClient" and the"last row" "LastClient" but i can never quite figure out the "dim" and "set to" and "FirstClient = " stuff...
I hope that all makes sense. It's a long way of asking a simple question.