#1
|
|||
|
|||
Creating multiple spreadsheets based on text
I'm looking to devlope a macro that will save myself a ton of manual hours.
I need to populate a form multiple times based on unique vendor name in Column A. Each vendor name should have its own form. Is this possible? It should take information from attached spreadsheet called "Test Data1" and populate attached, "Form" Here is the breakdown: "Test Data1" column A should populate "Form" sheet1, cell D8 "Test Data1" column B should populate "Form" sheet1, cell D12 "Test Data1" column C should populate "Form" sheet2, cell B6 "Test Data1" column D should populate "Form" sheet2, cell C6 "Test Data1" column E should populate "Form" sheet2 cell D6 Any help would be greatly appreciated. |
#2
|
|||
|
|||
HI,
Yes it can be done. You mention each "Vendor" has his own form. Will this be in the same workbook with mutiple sheets? Or, separate workbooks? |
#3
|
|||
|
|||
Separate workbooks and thank you!
|
#4
|
|||
|
|||
Hi,
Will the workbooks be in the same location? If so will you want the code to look thru it. Or, will you want to be able to select the file? |
#5
|
|||
|
|||
Quote:
The two workbooks will be in the same location. If that's what you're referring to? |
#6
|
|||
|
|||
HI,
I should have looked at the Data closer. I see that the "Vendors" have more than 1 invoice. How are you wanting to handle this? Added'' Your original post did not refer to creating workbooks. Are they existing? And the "Workbooks" will be in the same location. Last edited by charlesdh; 03-31-2015 at 02:18 PM. Reason: Ade info |
#7
|
|||
|
|||
Quote:
I want the file called "Form" be filled out for each vendor and populated with the info posted previously. Does that make sense? |
#8
|
|||
|
|||
Not sure if I understand.
You want a file called "Form" and in this file you want each vendors workbook. The workbooks will be named with the "Vendors" name. Now each vendor will have more than 1 Invoice. If thats the case you fill in data for 1 invoice. What do want to do with the other invoices for that vendor. I'm off to bed. But look at this tomorrow. Let me know if my assumption is correct and add anymore ino that you can. |
#9
|
|||
|
|||
Quote:
I want each vendor to have their own file. Once the macro runs, I should see the file "Form" for each vendor with Sheet1 and Sheet2 populated. It should populate as so... "Test Data1" column A should populate "Form" sheet1, cell D8 "Test Data1" column B should populate "Form" sheet1, cell D12 "Test Data1" column C should populate "Form" sheet2, cell B6 "Test Data1" column D should populate "Form" sheet2, cell C6 "Test Data1" column E should populate "Form" sheet2 cell D6 Sorry for any confusion. |
#10
|
|||
|
|||
Hi,
No need to be sorry. I'm getting closer to what you want. You want the info in the data worksheet to populate sheet 1 of the Form sheet and Sheet2. Now the new question is for example you have a vendor named "A&B Construction". It is listed several times. I can see that you will populate the form for the first record. Now you want to enter the next record for the same customer. How do you want to enter the second record? If you enter the next record it will over write the data in sheet1! Sheet2 would have no problems. It will go to the next row. |
#11
|
|||
|
|||
Quote:
Quote:
The next Vendor, should be C&D Plumbling will have their own workbook with Sheet1 and Sheet2 filled out on the Form and then Sunny Pipeline will have their own workbook. So at the end of this exercise, Ill have six workbook files. |
#12
|
|||
|
|||
HI,
I think I have it now. Will finish the code later today. |
#13
|
|||
|
|||
Really appreciate your time and assistance.
Curious, will I be able to use the code for future use? If I have the same spreadsheet but more rows? |
#14
|
|||
|
|||
HI,
I've gone as far as I can right now. The code in this attachment will loop through column A for the vendors. It should open the "Vendors" workbook. The workbook must be in the Vendors name. It can not be called "Form". It will set a filter for the vendors. I only tested for "A&B Company". Because that the only file I have. It will populate sheet1 and sheet2. You will need to change the "Path" in the code to your path. I added a "Exit Sub" in the code so you can only test 1 vendor at this time. Will you be adding more data to the "TestData" workbook for each vendor and leave the old data in place. Or when you done with the "TestData" will you clear it for new data? |
#15
|
|||
|
|||
Quote:
I wont be adding anymore data for the current vendors, but Ill be adding more Vendors and their info in the future. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Creating a table that automatically updates based on entries of a heading in the document | cahphoenix | Word | 3 | 10-29-2014 01:11 PM |
I need to add multiple values based on multiple criteria in a cell not sure what to do | AUHAMM | Excel | 3 | 10-27-2014 09:11 PM |
Need help creating a Word document that is populated based on certain selected values | alidaanish | Word | 1 | 01-10-2014 10:44 PM |
Creating Report based on 'Task Summary' and 'Sub Tasks' | alijahed | Project | 1 | 02-01-2013 04:20 AM |
Creating formula based on if data is correct in cell | MattMurdock | Excel | 1 | 08-06-2012 03:11 AM |