Transferring Rows of Multiple Data from one excel workbook to another

[chicity26]

Hello,

I am running into an interesting problem. I have two workbooks with similar columns (note: both workbooks may not have all the same columns, but for the same columns, the names are written identically in both workbooks.) I'll walk through what's in each sample workbook I've provided and what I am hoping to accomplish.

Sample Workbook 1: This workbook contains multiple rows of data for one company (For example, Client "A Co" has 4 rows of data.)

Sample Workbook 2: Contains some of the same columns as workbook 1, but not exactly in the same order.

What I want to do is take all the multiple row data for a company in workbook 1 and put the information as a single row in workbook 2. So, Client "A Co" in Workbook 1 has 4 rows, but in Workbook 2 will have 1 row. It's matching columns, essentially. Columns that are present in both workbooks should have the data filled out in only ONE row in Workbook 2. As far as cost is concerned, Cost A in Workbook 2 is just the sum of all the Cost A's for a particular client in Workbook 2 and the same for Cost B.

Notice, the column 'Products' in Workbook 1 is not in Workbook 2, and that's because each row for column may have different information and thus I would not be able to transfer it into a single row in workbook 2.

I can copy and paste information manually, but I am working with a huge dataset, so I was wondering, can anyone help me create a Macro or write VBA code to perform this action?

Thanks for your help.

[jindon]

Assuming Sample 1 & 2 are in the same folder.

[chicity26]

I am actually getting the following error with the code:

Capture.JPG

Both workbooks are in the same folder.

[jindon]

Working for me.