# Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

1. ## Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

[QUOTE=Mooseman60;3180827]Hi I am using Excel 2003 and have 27 different workbooks in the same folder in My Documents and would like to know how i can create a Macro to copy the same cells from each of the 27 workbooks to one new summary workbook. All the data is in the same cell numbers in all 27 workbooks.

Eg: The following summary workbook cells to save data too are A3, B3, C3, E3, F3

The cells from the 27 workbooks are E19, L10, L9, L15+L16

So A3 = E19 B3 = L10 C3 = L9 E3 = L17 F3 = L15+L16

I hope this makes sense See attached worksheet I have also posted this on Mr Excel but with no one able to help.

Building Q1 Total Q1 GM Workbook Q2 Total Q2 GM
E19 L10 L9 1 L17 L15+L16
E19 L10 L9 2 L17 L15+L16
E19 L10 L9 3 L17 L15+L16
E19 L10 L9 4 L17 L15+L16
E19 L10 L9 5 L17 L15+L16
E19 L10 L9 6 L17 L15+L16
E19 L10 L9 7 L17 L15+L16
E19 L10 L9 8 L17 L15+L16
E19 L10 L9 9 L17 L15+L16
E19 L10 L9 10 L17 L15+L16
E19 L10 L9 11 L17 L15+L16
E19 L10 L9 12 L17 L15+L16
E19 L10 L9 13 L17 L15+L16
E19 L10 L9 14 L17 L15+L16
E19 L10 L9 15 L17 L15+L16
E19 L10 L9 16 L17 L15+L16
E19 L10 L9 17 L17 L15+L16
E19 L10 L9 18 L17 L15+L16
E19 L10 L9 19 L17 L15+L16
E19 L10 L9 20 L17 L15+L16
E19 L10 L9 21 L17 L15+L16
E19 L10 L9 22 L17 L15+L16
E19 L10 L9 23 L17 L15+L16
E19 L10 L9 24 L17 L15+L16
E19 L10 L9 25 L17 L15+L16
E19 L10 L9 26 L17 L15+L16
E19 L10 L9 27 L17 L15+L16

2. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Hello mooseman60,

Are these workbooks the only files in the folder? If not, is there some pattern to the workbook names to distinguish them from any other workbooks?

3. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Ross

The folder is named RW645 and the only files in the folder are the 27 workbooks which are name RW645_1 to RW645_27

4. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Hello Mooseman60,

That will make this much easier. Thanks.

5. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Ross

The folder also contains the Summary Workbook as well

6. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Hello Mooseman60,

Okay, that's not a problem since the macro will be run from the Summary workbook.

7. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Hello Mooseman60,

Is column "D" used for anything?

8. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Make sure you specify the path to the folder you want to get the files from. Also , I am not sure what the name of the sheet you want to copy the data from, so I just called it sheet1, if it is a different name, change that too. The code is in Module one, make necessary changes there.

Edit: Sorry Leith , I did not see you had posted while I was writing the code.

``Please Login or Register  to view this content.``

9. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Hello Mooseman,

The attached workbook has the macro below added to it. All the workbooks in the folder will have their data from the cells you mentioned added to "Sheet1" starting on row 3. Each workbook's data is added directly below the previous the data with no blank rows. There is a butoon on "Sheet1" to run the macro.
``Please Login or Register  to view this content.``

10. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Originally Posted by JapanDave
Make sure you specify the path to the folder you want to get the files from. Also , I am not sure what the name of the sheet you want to copy the data from, so I just called it sheet1, if it is a different name, change that too. The code is in Module one, make necessary changes there.

Edit: Sorry Leith , I did not see you had posted while I was writing the code.

``Please Login or Register  to view this content.``

There seems to be a problem with this line of code

``Please Login or Register  to view this content.``
I have attached my Workbook

11. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

What is the sheet name in your 27 workbooks? Is it Sheet1?

12. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

The sheet name is Quote Template

13. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Whoops, my mistake, I left out the sheet name in my code.

try this.

``Please Login or Register  to view this content.``

14. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Dave

Still having problems this is the code i have inserted

``Please Login or Register  to view this content.``

15. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Can you post one of the workbooks you are trying to get data from?

16. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Please see attached workbook that lives in folder RW645

17. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

I can't open your zip file, can you post another way?

18. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

See if this one will open I am using Excel 2003

19. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

No This won't open either. Try uploading it via the forums tools. Use manage attachments, when you use "Go Advanced" when posting.
If the file is too big, just delete some data, I just want to see what the file looks like.

20. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

The file is 2.9MB so I have to zip it

21. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

When zipped it is an unrecognizable file. Delete most of the data and post normally.

22. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

I have deleted a lot of the data

23. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

In the sample workbook there is no sheet with the name "Quote Template" is this correct?

24. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

The worksheet I sent is only part of the original workbook, there are about 20 additional worksheets of data that the the work sheet that you have is used for reference using VLookup. The worksheet you have is actually called Quote Template when part of the whole workbook

25. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

In that case , where does the macro show an error?

26. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

If you go to Dropbox.com I have uploaded the full workbook

27. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

I just tested the below code and it works fine. Either your path is incorrect or the sheet name you are trying to get data from is incorrect. One last possibility is that you have saved the file which you run the macro from in the same folder as the 27 workbooks.

Is "RW645" the folder name or the workbook name?
``Please Login or Register  to view this content.``
If it is the workbook name you need to change it to this.
``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``

28. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Dave I have tried it several times but keep getting not responding

I have renamed the Folder from RW645 to EGRP as the Folder RW645 has a workbook called RW645 inside it, I thought this might have been the problem but still get same result.

I have saved the Consolidated workbook with the macro within Documents and not in the EGRP folder
This is the current Macro code

``Please Login or Register  to view this content.``

29. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Ok, lets try something else.

Save the following zip file to your desktop, create a new folder (Not zipped) and name it what you like, drag and drop the files in the zip folder to your new folder(The names of the workbooks must stay the same). Save the Consolidate file to your desktop as well.

Now open the consolidate_Ver2 file and and run the macro, you will be asked to select a file. Navigate to the folder you just created and pick any of the three files(it does not matter which one).
The macro should run and have created a new workbook with 3 workbooks data in it. Lets see if this works first.

30. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Dave that works perfectly on the desk top. When I open the Consolidation workbook and click the macro and select folder EGRP in Documents and click on a file name it goes to runtime error 76. The only thing I can think of is that my Files all start with RW does this make a difference to your code. Eg: RW645_1, RW645_2 where as your files don't have the RW in front

31. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

That code was only a test to see if you had the path correctly entered into the file, unless you will be changing folders all the time you don't want to always have to navigate through the folder picker.

The below code will enable you to pick and file in your folder that you want to get data from and not worry about how long its name is.
``Please Login or Register  to view this content.``
However if you don't want to navigate through the file picker, let me know and I will amend the code and show you how to get the correct file path of your folder.

32. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Dave

I loaded the new code and when I click on the macro and select the folder and file it just sits there with the hour glass and when you click anywhere in the workbook it says Not Responding

33. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Do you have any passwords in the workbooks? try moving the folder to desktop and see what happens?

34. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Dave

I tried it on the desktop and same result. I am uploading both Folder and files to Dropbox so you can see what is happening

35. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Ok, I was able to open your actual file and it takes a stack of time to open. I timed the procedure on my machine which has 16 gig of memory and it still took over 3 mins to run. Can you run the macro for about 30 sec and then press Ctrl + Pause Break key at the same time. This will forcibly stop the code. Then can you tell if any data has been transferred to the report workbook?

To give you an idea, I can run this code through about 70 workbooks in under 30sec.

36. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Dave

Ok I tried stopping the code and it does show data from the folder like it should. I let it run for about 3 minutes and it showed all the data from all the files correctly. I am using Windows Vista Home Edition on an Asus laptop with 2.53 GHz and 4GB ram and a 32 bit system. Looks like my laptop may not be powerful enough and I will just have to wait the 3 minutes for the download. Thanks very much for your patience, time and efforts in getting this macro to run it is very much appreciated. I will mark as solved.

37. ## Re: Macro To Copy Specific cells From Multiple Workbooks to New Summary Workbook

Good to know. I think your workbooks are pretty heavily loaded with formulas , so it is a necessary evil. Cheers

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1