+ Reply to Thread
Results 1 to 5 of 5

[Solved] Consolidating of Excel files

  1. #1
    Registered User
    Join Date
    02-24-2012
    Location
    MUMBAI
    MS-Off Ver
    Excel 2003
    Posts
    3

    [Solved] Consolidating of Excel files

    Hello,

    I want to consolidate the excel files data on sheet 1 to a new workbook one below the another.

    Daily i have 10 files to consolidate in a new excel workbook to work on.

    Please help me in getting a macro to copy and data and fixed it into a common excel file.

    Attached is the excel files to be consolidated.

    Thanks
    Abdul
    Attached Files Attached Files
    Last edited by Abdul Salam; 02-27-2012 at 12:06 PM. Reason: SOLVED

  2. #2
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Consolidating of Excel files

    I am assuming all the reports are xls files (Excel 2003) and you are using Excel 2003. The workbook I have attached has a button that when pressed will allow you to navigate to the directory containing the report files you want to combine into one report. All the files you want to combine into one report should be in a single folder. Once you select the folder with all the report files the code will copy all the data from the report files (assuming the data as in your example is in Sheet 1) and place it into a new workbook. The workbook is created in the subdirectory called Output. If the Output directory does not exist then it will be created. The workbook I create that is a combination of all the report files is named CombinedReports and I append the current date to the filename so it is a unique file. If the macro is run a second time I create a new file called CombinedReports and append the current date as well as the current time so that way the files do not get written over. Once the combined report is generated a message box will pop up advising you if the process has been successful and then it will open the combined report workbook for you.
    Test this on some test files first and make sure you have backups. The code will not affect the report files/overwrite them or change them however I recommend doing backups just in case until you are confident the code does what you require. Every day you want to run the process of combining the reports just open the attached workbook and press the button to execute the code. This workbook can be placed in any directory however I would recommend NOT placing it into the directory with the report files - place it into it's own Folder. As I say as you have to do this daily then I think naming the file with the current date will allow you to identify the combined reports easily. The code is as follows.
    Please Login or Register  to view this content.
    If you use Excel 2007 then the code would need to be modified slightly so if you use Excel 2007 let me know. Only a few lines would need changing.
    PS: There is a small amount of code in Worksheet 1 that starts the code in the module
    Attached Files Attached Files
    Last edited by smuzoen; 02-25-2012 at 01:35 AM.
    Hope this helps.
    Anthony
    Pack my box with five dozen liquor jugs
    PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated?

  3. #3
    Registered User
    Join Date
    02-24-2012
    Location
    MUMBAI
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Consolidating of Excel files

    Thank you so much, this is thing i was looking at.
    Bang on target.

  4. #4
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Consolidating of Excel files

    Fantastic. Glad to help. If your problem is now solved could you please mark the thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear so just ask a moderator to mark it as solved. If you are happy with the answer then please rate the solution by pressing the Star button in the lower left corner of your post.
    If you have any problems let me know

  5. #5
    Registered User
    Join Date
    02-24-2012
    Location
    MUMBAI
    MS-Off Ver
    Excel 2003
    Posts
    3

    SOLVED---Consolidating of Excel files

    [SOLVED]Thank you so much.
    Last edited by Abdul Salam; 02-27-2012 at 12:04 PM.

+ 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