VBA Code to copy paste in workbook from another Workbook


Hi There,
I am a new member!!

I have Workbook "ABC" where i am working on weekly based and this workbook has a ID number of Employees.

I want to use VBA code to get data from any other workbook "CDE" and paste in "ABC" Workbook if "ABC" workbook ID and Other Workbook "CDE" ID match.

How this should work?

1. Manual Open "ABC Workbook.
2. Run Code (Manual Run, I can do it. I will Use button to do it.)
3. Browsing to select the another workbook "CDE" from any folder from where i want.
4. Open Selected Workbook "CDE" and select Sheet.
6. Ask for Index Range and Match Range from "CDE/New Open Workbook" to match "ABC". (create formula =Index(SheetName!B1:B5,Match(A1,SheetName!A1:A5,0))
7. Ask Paste Formula or Value.

Please note within this process, What if i have opened the two workbook. like "ABC", "EFG" Another Workbook. As above mention process it will be 3 Workbook. i don't want close workbook where i am working even 3 or 4 workbook.

Thank you!!




It looks like you have the wrong setup. You should have a database with all data of all employees in a single sheet. As you correctly point out, you could draw on these data with a simple VLOOKUP using the employee ID as a key, for example. The files from which you now wish to draw that information instead should themselves take it from that database.

The code you describe might be suitable to actually create such a database from the dispersed information presently existing. If that is the intention it would be used only once which wouldn't be worth the effort of writing it. On the other hand, if the intention is to avoid creating it, keeping the dispersed system you now have, you would be making an effort aimed at preserving the mistaken setup investing the same effort in the venture that would be required to correct it.



I undarstand this point. but why i want coding is if i give this file to  my office staff i dont want them to change anything. i want them to work with range only, not to create other formula. and their file location and my file location may not be same because of user name.
Chandra87 May 2, '18 at 4:27 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login