Using VBA to copy/past from multiple workbooks

[joek128]

Hey guys.

I made a post here yesterday and I think my question turned out to be more confusing than it needed to be. So here goes another shot...

I'm looking to pull data from multiple workbooks into a master file with the following layout:

Name	2010 Salary	2010 Bonus	2011 Salary	....
Joe
John
Emily
....

<tbody>
</tbody>

The Number of employee names and column headers can increase/decrease depending on the data needed.

Each employee has a file on the C drive under "C:\[Employee Name]\Information\[Employee Name].xl"
The tab [Summary] contains data in the following format (the same format for each file, however some employees only have 1,2,3 etc years of past data)

	2009	2010	2011
Salary	$20000	$20000	$25000
Bonus	$5000		$5000

<tbody>
</tbody>

I've been trying to figre out the best way to create a VBA loop that will open the file based on the individuals listed in column A and paste data based on the headers in row 1. I've been trying to do this using an Index/Match function but would like to see if this is possible with VBA as it has become increasingly difficult to do it using normal function on Excel.

Hopefully this is more clear than my question asked last night.

Thanks in advance for the help.

 

[joek128]

solved

 

[sumits]

how did you solve this

 

[joek128]

I used a nested loop. First loop opened each file based on the next name in the list (offset loop). Second loop functioned as an index/match on the active workbook. I simply used an index match to locate the value I needed, defined it as a new variable, and even entered the variable to the correct column on the master workbook using another offset function.

It's at the office so I'll have to wait till tomorrow to copy the syntax, but I'm happy to share what I wrote.