Copying a cell, from one workbook and pasting in a another workbook...

[royal99]

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

[Simon Lloyd]

Show us what you have so far!, show us your code and where you are getting stuck.

[royal99]

Show us what you have so far!, show us your code and where you are getting stuck.

Set x = Workbooks.Open(" path to copying book ")
Workbooks.Open(" path to copying book ").Activate
Range("E6").Select
'Cells.Select
Selection.Copy
Set y = Workbooks.Open("Consolidated")
Workbooks.Open("Consolidated").Activate

With y
Sheets("sheetname").Cells.Select ' Here I want to search for "Name" in the list
Range("A1").PasteSpecial
'Sheets("sheetname").PasteSpecial
.Close
End With

With x
.Close
End With

'but this should be in loop.

[royal99]

Shown above image is

Image 1: Consolidated Workbook.xlsx

Image 2: Workbook1.xlsx

Hope this helps

Thanks
royal99

[royal99]

Hey Simon, I have attached a sample file, could you help me on this. your help will be appreciated...!

[nasser]

Shown above image is

Image 1: Consolidated Workbook.xlsx

Image 2: Workbook1.xlsx

Hope this helps

Thanks
royal99
thanks alot