Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Code to copy paste in workbook from another Workbook

0

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!!

Answer
Discuss

Answers

0

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.

Discuss

Discussion

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