Importing a specific range of cells from multiple workbooks to a single workbook

[exangel7]

Hello,

At the beginning I want to thank you all, who share their knowledge and help in all these threads. Many times I found here answers on my problems. But now, I need to face with hard thing to do in excel. I believe to find here some good advises how to do it.

Import specific information (specific range of cells) from only one worksheet (always it is sheet1, but called "Person") from differently named workbooks and put this information in specific range in active worksheet "Summary" in workbook "Data.xls". In table as you can see first line, paste manually.
As you can see, in file Data.xls there is a macro. I am completely beginner in VBA and thats all I found in web and make many times copy and paste to get this result. It's working, but not make job I want to get. Macro copy all data from all worksheets and insert it as new worksheets in this workbook "Data.xls".
I have of course more files to import. All have different names, but names of worksheet from which I wonder to get data is this same in all files.

I am really appreciate for any help which will bring to solve my problem.

Please Login or Register  to view this content.

[AB33]

You have lots of call functions.
I have adjusted the code to work with sheet named called "Person", but ONLY this section, and the code ,as you pointed that, copies all cells, you need to specify which cells you want to copy.

Please Login or Register  to view this content.

[exangel7]

Thanks for this adjustment, but after this changes there is "Compile error: Invalid or unqualified reference"
Any other solution how to copy only one worksheet? Not all?

Where to put cells which I want to copy?
How to tell excel where it should be paste?
(C2, C4, C5, C6, F2)

Copy from "Person"	Paste to "Summary"
C2	column B (start in B9)
C4	column C (start in C9)
C5	column D (start in D9)
C6	column E (start in E9)
F2	column F (start in F9)

[AB33]

This is my understanding of your request.
You want to loop through the files and then copy the above cells from each book(sheet name person) in to new worksheet. Is this right? If it is, the code you have got some lines which you do not need for this purpose. For e.g. you have select case

[exangel7]

Yes, you've right. I am beginner in VBA. This is absolutely new for me. I am learning on this case and cannot cope with this alone. It is many lines of codes which I found when looking any solutions in web. That's why there is possible too many codes. Please, show me which is not necessary.
After solve this case I am going to start deeply learn VBA. Unfortunately this solution I need faster then I can learn.

[AB33]

Okay! Will do

[AB33]

Please Login or Register  to view this content.

This line is very important, you need to change it to your own path. In this code, I used my own path, change it.
I have also included another code which you wish to use it to copy all rows, but for this task, the first(top) code on the module is relevant one.

[exangel7]

Thanks a lot. It works perfectly!

You wrote new lines of code. Wow. Respect for you and your knowledge.

I attach here all final job. Maybe someone else find here this solution.
Once again, many thanks.

[AB33]

Exangel7,
You are welcome!
Could you please now close (Mark) this thread as solved? Go to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.