Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Combining several workbooks

    I have several workbooks containing the same heading in Cols A to G

    I would like to know how to combine these into one workbook using consoluidation or a macro that will copy the data into one workbook if all in one folder. I download the data each month from different branches and would like to combine these into one workbook once downloaded

    I have also posted on Mr Excel.com

    http://www.mrexcel.com/forum/excel-q...workbooks.html

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Howard

    This can be done by modifying one of my previous posts.
    I'll do the mods and post the result back for you.

    zeddy

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Thanks

    Thanks, appreciate your help

    Regards

    Howard

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Howard

    Before I post back, are you using Excel2010? or Excel 2007?
    ..or do you want the merge file to be .xls format?
    I'm assuming your datafiles are .xls?

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Howard

    I seem to remember you using Excel2010, so my attached zip file assumes you have that.
    The Branch datafiles I assume will be in .xls format.

    OK, I have created a sample Branch Merge Tool which shows how to merge data from up to 100 specified datafiles into a Consolidation File, using the structure of your file.

    In my Branch Merge Tool, I have included named cells which allow you to specify where data is located.
    A 'clicker' is also provided to set the destination folder for any monthly Consolidation file.
    You will need to amend the vba code to make use of these, but it is a simple task. Ask if you need further help with this.

    For the purposes of this example, I have assumed that everything in my zip file will be placed into a folder named C:\TEST\
    Using the zip file:
    First, create a folder on you C:\ drive named
    C:\TEST

    Then Extract all files from the zip file into C:\TEST\

    The attached zip file contain the Master processing Tool for merging the datafiles:
    [rzBranchMergeTool-v1.00.xlsb]

    There is a blank template file used for the merged data from the datafiles:
    [BlankBranchConsolidated.xlsb]

    To create a new datafile for any Branch, simply copy a blank template datafile and re-name it with the name of the Branch, for example:
    [Data-Blank.xls] >> [Data-CarsRus.xls] etc (use whatever naming convention you prefer)

    The zip file contains 3 sample datafiles, corresponding to sample Branch names entered into the Branch List in the merge Tool.

    Open the file [rzBranchMergeTool-v1.00.xlsb] with macros enabled.
    Click the button labelled [click here to Merge..] and then watch as the status cells are updated as each datafile is merged.

    You should be able to adapt the blank files to match your setup.
    I have commented the vba code to explain what is going on at every step.
    This should enable you to adapt this for your needs.

    For fun, I included in the processing the number of vehicles per Branch, Total Retail stock values etc etc

    Any questions, please ask..

    zeddy
    Attached Files Attached Files

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for all the effort and your explanations. I will test over the weekend & let you know

    Regards

    Howard

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for allthe effort and develioping the branch merge tool.

    I wanted to test this morning, but have Office 2007 at home so will have to twst at the offfice next week. Also If I add additional columns to the data files, where do I change this on your macro to accomodate any additional columns?

    Regards

    Howard

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Howard

    You can use the Tool with Excel 2007, Excel2010, Excel 2013.

    The Branch Merge Tool uses [a1].currentRegion method to 'grab' data from the Branch datafiles, which means that there are no vba code changes required if you subsequently alter the number of columns (provided, of course, that the columns are all adjacent).
    So as long as you use the same headings in the blank consolidation file as are used in the Branch data files you will be OK. Neat eh!

    So you will be able to test with your Office2007 version.

    zeddy

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    I created the folder C:\test and the saved your zip file into this folder and then unzipped the files. When I open the rzbranchmerge workbook , i get a message excel found unreadable content in rzbranchmergetool. see attached screenshot.

    It would be appreciated if you could advise on how to resolve this
    Attached Files Attached Files

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Howard

    It seems to be a cosmetic issue with the chocolate colours I'm using.
    I opened it OK on my Excel2007.
    See attached screenshot doc to see what it is supposed to look like.
    I was using cell background colours via
    Home>[Cells]Format>Format Cells>[Fill][More Colors..]>[Custom] [RGB] Red:76;Green:51;Blue:39 etc

    I will make an adjusted version using the default Excel2007 Colour Palette, and see if that works for you.
    Curious you got that error. I would be very interested if any other person on this Forum gets same result with that file and Excel2007

    Will post back a revise file shortly. I'll test it on my system before posting.

    zeddy
    Attached Files Attached Files

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    When I opened the merge workbbok on my work PC yesterday , I could open the workbook with no problem. I have time over the weekend to test your file and for you to fine tune where required

    Regards

    Howard

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Howard

    I am attaching a version which (hopefully) just uses the standard theme palette of Excel2007.
    Also, it's in .xlsm format, which shouldn't make a difference.
    Please copy this attached file to your C:\Test folder.
    Open the file, enable macros.
    Click the [Clear Status results] button to clear previous results.
    Then click the [click here to Merge data files..] button to see the merge process working.
    It should be fast.
    You should be able to easily adjust the formulas in [G16:G115] to suit your own file naming convention.

    Please let me know how this one works out.
    And what time zone are you in?
    Don't you ever sleep????

    zeddy
    Attached Files Attached Files

  13. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the update will test & advise. I am in South Africa and am 1 hour behind the UK. Believe it or not, I do sleep, but am am early riser (anytime between 4 and 5am). When I was at school and university I battled to get at in the mornings, but I try to go to the gym for an hour 3-4 times a week, so by 9:30pm i'm in bed during the week.

    Regards

    Howard

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi Howard

    OK that explains it! I was thinking 5 hours behind in USA East Coast or 8 hours behind(?) in USA West Coast, and either way couldn't work out whether you were still up from yesterday or just going out.

    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-08-03)

  16. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    The rzbranchmergetool workbook opens corretly now. After clicking the button merge data file an error message (run time error 1004) appears and the folowing code is highlighted

    Workbooks.Open Filename:=zFetch, ReadOnly:=True 'open read-only copy of template file


    I then opened the workbook Blankbranchconsolidated and get same error mesage that I got before you corrected the rzbranchmergetool workbook-see attached screenshot

    It would be appreciated if you could test & correct

    Regards

    Howard
    Attached Files Attached Files
    Last edited by HowardC; 2013-08-03 at 09:33.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •