+ Reply to Thread
Results 1 to 37 of 37

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

  1. #1
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    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?

    NOTE: Your workbook did not attach to your post.

  3. #3
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Mooseman60,

    That will make this much easier. Thanks.

  5. #5
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

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

    Ross

    The folder also contains the Summary Workbook as well

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    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. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

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

    Hello Mooseman60,

    Is column "D" used for anything?

  8. #8
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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.
    Attached Files Attached Files
    Last edited by JapanDave; 06-17-2012 at 01:31 AM.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    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.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

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

    Quote Originally Posted by JapanDave View Post
    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
    Attached Files Attached Files

  11. #11
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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. #12
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

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

    The sheet name is Quote Template

  13. #13
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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.
    Attached Files Attached Files
    Last edited by JapanDave; 06-17-2012 at 07:10 AM.

  14. #14
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #15
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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. #16
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

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

    Please see attached workbook that lives in folder RW645
    Attached Files Attached Files

  17. #17
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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. #18
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

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

    See if this one will open I am using Excel 2003
    Attached Files Attached Files

  19. #19
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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. #20
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

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

    The file is 2.9MB so I have to zip it
    Attached Files Attached Files

  21. #21
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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. #22
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

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

    I have deleted a lot of the data
    Attached Files Attached Files

  23. #23
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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. #24
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #25
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

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

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

  26. #26
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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

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

  27. #27
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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.
    Last edited by JapanDave; 06-17-2012 at 08:54 PM.

  28. #28
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #29
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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.
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #31
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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.
    Attached Files Attached Files
    Last edited by JapanDave; 06-18-2012 at 01:45 AM.

  32. #32
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #33
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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. #34
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #35
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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.
    Last edited by JapanDave; 06-18-2012 at 03:15 AM.

  36. #36
    Forum Contributor
    Join Date
    09-26-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    182

    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. #37
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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