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

**Mooseman60** · 06-16-2012, 10:03 PM

[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

**Leith Ross** · 06-16-2012, 10:10 PM

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?

NOTE: Your workbook did not attach to your post.

**Mooseman60** · 06-16-2012, 10:15 PM

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

**Leith Ross** · 06-16-2012, 10:16 PM

Hello Mooseman60,

That will make this much easier. Thanks.

**Mooseman60** · 06-16-2012, 10:18 PM

Ross

The folder also contains the Summary Workbook as well

**Leith Ross** · 06-16-2012, 10:20 PM

Hello Mooseman60,

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

**Leith Ross** · 06-16-2012, 10:23 PM

Hello Mooseman60,

Is column "D" used for anything?

**JapanDave** · 06-16-2012, 10:26 PM

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.

**Leith Ross** · 06-16-2012, 10:50 PM

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.

**Mooseman60** · 06-17-2012, 05:26 AM

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

**JapanDave** · 06-17-2012, 06:43 AM

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

**Mooseman60** · 06-17-2012, 06:52 AM

The sheet name is Quote Template

**JapanDave** · 06-17-2012, 07:01 AM

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

try this.

Please Login or Register  to view this content.

**Mooseman60** · 06-17-2012, 08:28 AM

Dave

Still having problems this is the code i have inserted

Please Login or Register  to view this content.

**JapanDave** · 06-17-2012, 08:56 AM

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

**Mooseman60** · 06-17-2012, 09:00 AM

Please see attached workbook that lives in folder RW645

**JapanDave** · 06-17-2012, 09:07 AM

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

**Mooseman60** · 06-17-2012, 09:20 AM

See if this one will open I am using Excel 2003

**JapanDave** · 06-17-2012, 09:22 AM

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.

**Mooseman60** · 06-17-2012, 09:39 AM

The file is 2.9MB so I have to zip it

**JapanDave** · 06-17-2012, 09:43 AM

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

**Mooseman60** · 06-17-2012, 10:01 AM

I have deleted a lot of the data

**JapanDave** · 06-17-2012, 10:09 AM

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

**Mooseman60** · 06-17-2012, 10:28 AM

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

**JapanDave** · 06-17-2012, 10:33 AM

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

**Mooseman60** · 06-17-2012, 10:47 AM

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

Username: roofworksportable@iinet.net.au Password: Aussie1960

**JapanDave** · 06-17-2012, 08:51 PM

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.

**Mooseman60** · 06-17-2012, 10:59 PM

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.

**JapanDave** · 06-17-2012, 11:50 PM

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.

**Mooseman60** · 06-18-2012, 12:40 AM

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

**JapanDave** · 06-18-2012, 01:31 AM

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.

**Mooseman60** · 06-18-2012, 01:52 AM

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

**JapanDave** · 06-18-2012, 02:18 AM

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

**Mooseman60** · 06-18-2012, 02:34 AM

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

**JapanDave** · 06-18-2012, 03:13 AM

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.

**Mooseman60** · 06-19-2012, 03:11 AM

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.

**JapanDave** · 06-19-2012, 07:38 AM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions