+ Reply to Thread
Results 1 to 14 of 14

Copy range from multiple files in multiple folders to single sheet in master WB

  1. #1
    Registered User
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    97

    Smile Copy range from multiple files in multiple folders to single sheet in master WB

    I have 30 files, each in a different folder on a network drive. I need to be able to copy the top 100 rows from the first sheet of each file and paste them into a single sheet in a master file on a monthly basis. (If that is not possible, I guess I could copy them to separate sheets and link all of the sheets to a "master" sheet in my master workbook.)

    There are other Excel files in these directories, but the files I need to copy this range from share the word "Source" in the workbook name.The sheet name of the first sheet in every file is "CASH".

    The copy and paste from the first file would fill rows 1 - 100 on the master sheet. The data from the second file would paste on rows 101 - 200 in the master sheet, and so forth. After all the data has been copied and pasted the file would automatically save. Some directory examples:

    S:\Accounting\Film\WOLB\WOLB Source File.xls

    S:\Accounting\Film\WITX\WITX Source File.xls

    S:\Accounting\Film\WBBB\WBBB Source File.xls

    If you can help me I would REALLY appreciate it. I've been unable to find a solution to this anywhere.

    Thanks!

  2. #2
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    @Royzer

    A couple of points...
    link all of the sheets to a "master" sheet in my master workbook.
    1. I take that to mean you only need values transferred.
    2. There is no mention of the number of columns to transfer so I have made the columns to transfer as the last data column of each source wsheet (which may be greater than the last column of rows 1 to 100)

    On that basis try this out...

    Please Login or Register  to view this content.

    hth

    gmk



    Click the * below the post to say thanks and remember to mark the thread as solved if answered satisfactorily.
    Last edited by gmk; 07-18-2012 at 04:12 AM.

  3. #3
    Registered User
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    97

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    Thank you so much for your help, GMK.

    I am getting this message "Compile Error: Sub or Function not Defined" when it gets to SetCurrentDirectoryA under Errhandler:
    Also, sorry I did not specify the range. It needs to be A3:N103.
    Last edited by Royzer; 07-18-2012 at 09:52 AM.

  4. #4
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    @Royzer
    I am getting this message "Compile Error: Sub or Function not Defined" when it gets to SetCurrentDirectoryA under Errhandler:
    My fault, I did not include the function declaration.

    But first can you clarify...

    I need to be able to copy the top 100 rows from the first sheet of each file
    The copy and paste from the first file would fill rows 1 - 100 on the master sheet

    Also, sorry I did not specify the range. It needs to be A3:N103.
    As you want rows 3 thru 103 and not 'the top 100 rows from the first sheet of each file' can you please clarify which row the data should be copied to in the Master wb. Is it row 1 onwards or row 3 onwards?

    Thank you.

  5. #5
    Registered User
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    97

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    Sorry, again. The Master WB would have two rows of headings, so the incoming data would need to go on row 3 onwards.

  6. #6
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    @Royzer

    Try this one.
    Note I have also included the original 'close parent wb and save' request after the message functions but have commented it out. To enable the 'close' just remove the single quote at the beginning of the line.


    Please Login or Register  to view this content.


    hth

    gmk



    Click the * below the post to say thanks and remember to mark the thread as solved if answered satisfactorily.

  7. #7
    Registered User
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    97

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    Thank you!

  8. #8
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    @Royzer

    I'm pleased we got there.






    hth

    gmk



    Click the * below the post to say thanks and remember to mark the thread as solved if answered satisfactorily.

  9. #9
    Registered User
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    97

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    The code is running, but I am getting the message There are no subfolders in the path - 'S:\Accounting\Film\'.

    This is the true path to three files I am using to test:

    S:\Accounting\Film
    (I copied this from the file properties box of one of the files.)



    Last edited by Royzer; 07-18-2012 at 12:36 PM. Reason: to show that message uses single quotes, not double

  10. #10
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    @Royzer

    Ok, remove the backslash and see what happens.

  11. #11
    Registered User
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    97

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    Getting the same message with and without that last backslash.

    ---------- Post added at 12:51 PM ---------- Previous post was at 12:45 PM ----------

    It has to be a problem with how my file location is set up. I changed the path to another directory, one that has no test files in it, and it returned the message Completed. No workbooks were transferred.

    I will fool around with it some more and see what happens.

    Thank you again for taking the time to help me!

  12. #12
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    @Royzer

    Are you ok using the VBE editor?

    Instead of numbers in the debug lines I provided earlier, to be more precise I've used names...
    Please Login or Register  to view this content.
    with these results...
    Please Login or Register  to view this content.
    You should see something similar.

    hth

  13. #13
    Registered User
    Join Date
    04-29-2010
    Location
    Tallahassee, FL
    MS-Off Ver
    365
    Posts
    97

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    I replaced the section of code as you instructed and tried it again on another path. I did not get the results you show above, BUT it copied the data exactly how I need it! And I figured out why it was working on some paths and not the path on the S:\Accounting\Film path. It was looking for a third folder, apparently. I added another folder and put the test files in it so that they were in here:

    S:\Accounting\Film\Film2

    but kept the VBA code as:

    Const sPath As String = "S:\Accounting\Film"

    and it works perfectly!

    GMK, I cannot thank you enough for your time and effort. I really appreciate it.

    Roy

  14. #14
    Forum Contributor
    Join Date
    04-12-2012
    Location
    MD, England
    MS-Off Ver
    Excel 2003
    Posts
    142

    Re: Copy range from multiple files in multiple folders to single sheet in master WB

    @Royzer

    I added another folder and put the test files in it so that they were in here: S:\Accounting\Film\Film2
    If you only put files into Film2 then Film2 acts like any of the other test folders WBBB, WITX, WOLB.
    If I put those folders containing files into Film2 as you suggest I got the message "There are no files in any subfolders." which is exactly what I would expect because there is only one recursion level.

    I did not get the results you show
    If you didn't get the same results the directory paths cannot be as described.

    Nonetheless, you do have a solution that works for you and that is the main objective.

    gmk

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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