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.

Attached Files

	Test Data1.xlsx (10.6 KB, 9 views)
	Form.xlsx (237.7 KB, 10 views)

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?

Separate workbooks and thank you!

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?

Are you referring to the workbooks created by the macro or the two workbooks that will create everything?

The two workbooks will be in the same location. If that's what you're referring to?

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.

If a Vendor has more than one entry, I want the info stated previously in one file.

I want the file called "Form" be filled out for each vendor and populated with the info posted previously. Does that make sense?

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.

I was referring to the workbook I attached called "Form" in my original post.

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.

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.

Yes, exactly.

I want the Vendor Name to populate cell D8 of Sheet1 on the Form and Vendor Number to populate cell D12 of Sheet1 on the Form. Then it would populate the Vendor Item, Description and Comments into Sheet2 of the Form in columns B, C and D. Example A&B Construction should have 8 rows on Sheet2.

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.

HI,

I think I have it now. Will finish the code later today.

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?

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?

Attached Files

TestData.xlsm (15.4 KB, 25 views)

I'm going to do some testing right now and see if it works properly, but just glancing over the code...WOW!

I wont be adding anymore data for the current vendors, but Ill be adding more Vendors and their info in the future.