Copy data in columns E:G to different workbook with worksheet = value in column C

Hello,
I am new to VBA. I have a master spreadsheet that has data populated in columns A to G. I would like a macro to loop through the data in columns E:G copying the data in each row to another workbook called Vendors.xlsx that contains a worksheet with the same name as the value in column C. The data should be pasted special values in the first empty cell starting in B3.

For example. From the below data, the data in row 2, columns E, F and G, would be copied and pasted into workbook Vendors.xlsx, worksheet Vendor1. Data in row 3 would be pasted into the worksheet Vendor2.

Thank you in advance for any assistance.

Column C Column D Column E Column F Column G
Vendor # Location Address City State
Vendor1 1 Main St Philadelphia PA
Vendor2 3 XYX St New York NY
Vendor3 1 ABX Rd Seattle WA
Vendor2 1 G St Trenton NJ
Vendor4 3 Race St Philadelphia PA
Vendor1 2 1st St New York NY

Hi Luff

This works as well. I've set it to copy from Sheet1 to the right Vendor sheet. Change if it's not right
I've attached a sample file
Let me know if it's no good

Please Login or Register  to view this content.

Thanks Philb1.

It works good expect for two issues. 1. The vendors have multiple locations. If I add additional rows for the same vendor, when the macro runs, it appears to be writing over the first location. Can you adjust the code so it pastes "special" in the next blank row? 2. If I add a header above the vendors, the macro runs to error because the header text is not a worksheet in the vendors.xlsx file. On the attached spreadsheet, I have hightlight the cells in yellow were the header would be.
Thanks Luffk73

Thanks for looking at this alansidman. I have close to 50 vendors so I don't want to have to manually type all the vendors into the code.

Hi Alan,

That is move the row the data is being pasted in down 1 row but still only 1 row of data is appearing.

Luffk73

Hi Alan,

The code is also using the "paste" function, not the "Paste Special - Values" function. Can that be changed?

Thanks,

Luffk73

Hi Alan,

I was able to get it to work by changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

how can I change this line to paste special?

Please Login or Register  to view this content.

Thanks

Luffk73

In my defense I was almost asleep when I posted last night
Here's Version 2 & hopefully works as you would like

Please Login or Register  to view this content.

V2 is attached for you to play with
Have fun

Thanks Alan!!!