royal99
02-18-2015, 10:33 AM
I have 6 or more workbooks in a folder and One separate workbook name "Consolidated" in the same folder.
Every workbook will have 6 or more sheets (dynamic) named like "John", "Donald", "Kate", "David", "Jim", "Sam". as first name ONLY
Each sheet will have some value in a cell "e24" as of now but it will be on the last row of the table, but the cell address may change in future
Now Consolidated workbook: This workbook will have list of full names in column B randomly arranged like below
Column 1 Column 2 Value
1. Kate D'silva 24
2. Sam Foster 27
3. Donald Duck 42
4. David Jackson 32
5. Jim Simon 19
6. John Mcdonald 31
Now the task
A macro which will open 6 workbooks one by one, then it will go in sheet 1 i. "John", it will remember from whose sheet the value is taken, then it will open "Consolidated" workbook, will open sheet1, search for the name "John" now on the basis of first name only it will have to search since in the previous workbook had sheet name "John" so it will paste the value in next column of that found name "John Mcdonald". Then macro will go to the second sheet name "Donald" copy the value from "e24", then go to "consolidated" file search for name "Donald Duck" and paste the value in next column.
The above macro will continue the same with rest of the 4 sheets or more then open a second workbook...., then third workbook.....this will go on till all the workbooks sheet data are copied in "Consolidated" file in that folder.
Thats it! Hope I have well explained.
I am facing with storing a sheet name in a variable, then going to consolidated workbook, then searching for name.....thats where I am stuck up...its again a loop...!
Thanks in advance
Every workbook will have 6 or more sheets (dynamic) named like "John", "Donald", "Kate", "David", "Jim", "Sam". as first name ONLY
Each sheet will have some value in a cell "e24" as of now but it will be on the last row of the table, but the cell address may change in future
Now Consolidated workbook: This workbook will have list of full names in column B randomly arranged like below
Column 1 Column 2 Value
1. Kate D'silva 24
2. Sam Foster 27
3. Donald Duck 42
4. David Jackson 32
5. Jim Simon 19
6. John Mcdonald 31
Now the task
A macro which will open 6 workbooks one by one, then it will go in sheet 1 i. "John", it will remember from whose sheet the value is taken, then it will open "Consolidated" workbook, will open sheet1, search for the name "John" now on the basis of first name only it will have to search since in the previous workbook had sheet name "John" so it will paste the value in next column of that found name "John Mcdonald". Then macro will go to the second sheet name "Donald" copy the value from "e24", then go to "consolidated" file search for name "Donald Duck" and paste the value in next column.
The above macro will continue the same with rest of the 4 sheets or more then open a second workbook...., then third workbook.....this will go on till all the workbooks sheet data are copied in "Consolidated" file in that folder.
Thats it! Hope I have well explained.
I am facing with storing a sheet name in a variable, then going to consolidated workbook, then searching for name.....thats where I am stuck up...its again a loop...!
Thanks in advance