+ Reply to Thread
Results 1 to 10 of 10

Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    UK
    MS-Off Ver
    Work 2007, Home2010
    Posts
    4

    Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    Hi All,

    I need some help getting a macro to run.

    I have a number of workbooks that I need to copy data from and place it in one workbook. The data in each source workbook is contained in one worksheet, but the size of the range (including starting cell) may be different each time. I want this data to be pasted into a new worksheet in the destination workbook for each source workbook. Ideally I'd like these not to be created each time as I will be using these sheets to generate pivot data and I'm not sure how the table/formula will react if the sheets are deleted/created each time the data is updated.

    I have gotten to a point where the macro will run and copy one or two worksheets worth of data in but breaks down giving a 'Run-time error '7': Out of memory' message. I assume this is to do with too much data being held on the clipboard, or the size of my copy/pastes due to the dynamic ranges.

    I'd be very grateful if you would have a look at the vba I've got so far. I took this from an MSDN page I think (sorry, I lost the source) and have butchered it from there, so apologies if it's horrific, I am very much a novice at this. If anyone can suggest an alternative way to write this, I'd be very happy to see it.

    Please Login or Register  to view this content.
    Last edited by Noeli0; 02-11-2015 at 04:19 AM.

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,668

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    Perhaps something like this?

    I've used your path but as this is a system disk I've had no chance to test if this works. Testing with a folder on a local disk on my PC works ok so you could try to add 3 - 4 to a folder on you local disk and run the macro "ProcessAllFiles" to see if the result produced is what you wanted. When changing folder name in macro dont forget to add the last "\" i.e. a macro with sPath given as "C:\test" will not work the correct string name is "C:\test\"

    The name "Master.xlsm" is hard coded in the macro so if you wish to chang this name you must change it in the macro as well.

    Alf
    Last edited by Alf; 02-12-2015 at 01:49 AM.

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,668

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    Seems like the file I tried to upload did'n make it.

    Well here we go again.

    Alf
    Last edited by Alf; 02-12-2015 at 01:50 AM.

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,668

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    Sorry but at the moment the forum don't seem to accept the uploading of files. Perhaps uploading will work tomorrow???

    Alf

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,668

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    Posting the code

    Please Login or Register  to view this content.
    Put code in a workbook named "Master.xlsm"

    Alf
    Last edited by Alf; 02-11-2015 at 12:10 PM.

  6. #6
    Registered User
    Join Date
    02-10-2015
    Location
    UK
    MS-Off Ver
    Work 2007, Home2010
    Posts
    4

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    Alf, thanks for all this, I'll check it out at work today.

    I meant to remove the file path before posting (have now edited). Would you mind removing the file path from your posts?

    Cheers

  7. #7
    Registered User
    Join Date
    02-10-2015
    Location
    UK
    MS-Off Ver
    Work 2007, Home2010
    Posts
    4

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    Alf,

    I've now had a chance to text this and it works pretty well, the only thing I'd like to change is that your code adds the data in as new sheets, where I'd like it to be pasted on existing sheets so the formulas that look at it will be preserved.

    My sheets are named A-M and are in index positions 4-16. Am I able to change this part of your code to make this happen?

    Please Login or Register  to view this content.
    Thanks very much for your help!

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,668

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    If you cut out the part as you have shown in post #7 then all copied results will be pasted to the active sheet on the target file but this is probably not what you wish fore.

    Could you explain a bit in more detail how you wish to paste the values in sheet A-M and index positions 1-6.

    Could the file names you extract data from be used in pasting to the "right" A-M sheet? Or is there no structure in the name?

    Alf

  9. #9
    Registered User
    Join Date
    02-10-2015
    Location
    UK
    MS-Off Ver
    Work 2007, Home2010
    Posts
    4

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    I actually figured itout this afternoon.

    The workbooks I am copying from all have titles prefixed with a letter corresponding to the sheet I want them pasted into (A-M). by changing your code to the below I managed to get it to work.

    Please Login or Register  to view this content.
    Thanks very much for the input, it has been incredibly useful!

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,668

    Re: Copy Data from One Sheet in Multiple Workbooks into Multiple Sheets in One Workbook

    You are welcome.

    Thanks for feedback.

    Alf

    Ps As this solves you problem could you please marke thread "Solved"

    To mark your thread solved do the following:
    -Go to the top of the first post
    -Select Thread Tools
    -Select Mark thread as Solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-23-2014, 06:42 PM
  2. Replies: 0
    Last Post: 07-29-2013, 03:00 AM
  3. [SOLVED] copy data in one sheet to multiple sheets in same workbook
    By BrianMultiLanguage in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  4. [SOLVED] copy data in one sheet to multiple sheets in same workbook
    By BrianMultiLanguage in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  5. copy data in one sheet to multiple sheets in same workbook
    By Alan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM

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