+ Reply to Thread
Results 1 to 6 of 6

Consolidate all data from multiple files into single excel file

  1. #1
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Consolidate all data from multiple files into single excel file

    Hi All,

    I have dozens of excel files (each having number of sheets). There is exactly the same data in all files and sheets with the same headers

    1. All the files are named differently
    2. All the workbooks have the same number (4) of sheets with same names ("001", "004", "058", "002").

    I would like to write a vba code that all data from different files will be consolidated into single new excel file without actually opening the files???

    I need this as I have dozens of files and it will take a long time to manually copy paste data manually.

    I will be very thankful for your help.

    See attached the sample of one of those files.
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,318

    Re: Consolidate all data from multiple files into single excel file

    Do you want to place each sheet from each source files to a sheet with the corresponding sheet name in the destination file? This would mean that the destination file also has the same four sheets. What is the full path to the folder containing the files and what is the extension of the files (xlsx, xlsm)? Are the source files the only files in that folder?
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Re: Consolidate all data from multiple files into single excel file

    Hi and thank you for your reply.

    1. The destination file will have the same number of sheets and names.
    2. The full path of source files is C:\Users\emil abrahamyan\Desktop\Due Diligence Creation and extension is xlsx
    3. Yes, the source files will be the only files in the folder.


    P.S. Please note, that although the source files will have the same number of sheets and names, there might be sheets which do not have any data at all- therefore this should be skipped.

    I hope this answers to your question.

    Thank you very much.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,318

    Re: Consolidate all data from multiple files into single excel file

    This macro assumes that there are 4 sheets in the destination workbook with the same names as the sheets in the source workbooks. It also assumes that the four sheet in the destination workbook already include the same headers in row 1.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-01-2020
    Location
    Yerevan
    MS-Off Ver
    2010
    Posts
    44

    Re: Consolidate all data from multiple files into single excel file

    HI and thank you for your reply.
    I have tried it but there is runtime error 9 and the line starting with ws.UsedRange.Offset is highlighted.

    Could you advise on this please? thanks

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    7,318

    Re: Consolidate all data from multiple files into single excel file

    Are the sheet names in the destination workbook the same as those in the source workbooks?

+ 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. Extract data – Multiple Files – to Single File – in Excel
    By Nostradamus in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 03-11-2017, 12:00 PM
  2. Consolidate data from multiple excel files into single file
    By hkkk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2013, 03:34 AM
  3. Consolidate data from multiple excel files into single file
    By hkkk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2013, 12:36 AM
  4. [SOLVED] Pull data from the same cell across multiple csv files and place into a single excel file
    By rbmrrbmr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-18-2013, 10:53 AM
  5. How to consolidate data from multiple excel files into single sheet in new book?
    By Pradeep M B in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-07-2013, 09:37 AM
  6. [SOLVED] Macro to read the multiple csv files and consolidate into single excel
    By parthmittal2007 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-03-2013, 03:40 AM
  7. Consolidate Data from Multiple Files (Different File Names) into a Master File
    By dspraveen_23 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-08-2012, 12:59 PM

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