Results 1 to 10 of 10

Thread: Combining workbooks

  1. #1
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Combining workbooks



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I have about 80 workbooks. I need to combine them into one workbook.
    I only need 3 columns a9:a87, b9:b87 and fc9:fc87.

    These workbooks data changes daily so I need to be able to pull the updated data daily. Let me know. Thanks in advance.


    Sent from my iPhone using Tapatalk

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,597
    Articles
    0
    Excel Version
    365
    Each workbook has only one sheet?
    They're all in the same folder?
    What version of Excel do you have?

    Sent from my lawnmower using hot water

  3. #3
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by p45cal View Post
    Each workbook has only one sheet?
    They're all in the same folder?
    What version of Excel do you have?

    Sent from my lawnmower using hot water
    Each work book should only have 1 sheet in case there is a more then one sheet though it would be the first sheet that I need.

    The files are in one folder but each file is in a sub folder. But I can easily get them in the same folder

    Iím using Microsoft office 365 pro plus


    Sent from my iPhone using Tapatalk

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    186
    Articles
    0
    Excel Version
    2019
    Accomplished using Power query. Ensure that the files are all in the same folder. Here is a link

    https://www.youtube.com/watch?v=a7E29H5ZUmE

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,597
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by chrisl614 View Post
    I need to combine them into one workbook.
    Can you confirm whether it's into one workbook or one worksheet?

  6. #6
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    12
    Articles
    0
    Excel Version
    2016

    Combining workbooks

    Quote Originally Posted by p45cal View Post
    Can you confirm whether it's into one workbook or one worksheet?
    I want to get them all into one work sheet. But I only need I only need the values from 3 columns a9:a87, b9:b87 and fc9:fc87


    Sent from my iPhone using Tapatalk

  7. #7
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Didn't expect to have this much trouble with this project. Would it be easier to import all the files into access and just export them to excel? (I'm not too familiar with access)

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,597
    Articles
    0
    Excel Version
    365
    In the attached are two solutions (two buttons).
    Since there are 80 or so files I looked for speed with Solution 2 below.

    Solution 1 (slower)
    The button runs the macro blah which opens each workbook one at a time and copies data from the first sheet.
    It uses a plain cut and paste so if there are formulae in the ranges you're copying from you'll get them too (I think) as well as any formatting. If you want to be more specific about copying values and perhaps some formatting it shouldn't be hard for me to tweak the existing code.

    Solution 2
    This runs blah2 and only puts links (formulae) into your resutls sheet then converts the results of those formulae to plain values. It doesn't open the workbooks and so is considerably faster.
    However, it does assume that the sheet you're copying from is called Sheet1. If they're not then a little dialogue box will pop up asking you to click on the correct sheet name, but if there are 80 files you'll be doing this a lot. which is tiresome.
    So if (mostly) all the sheets you want to copy from have the same name, you can edit the blah2 macro, finding the line:
    .FormulaArray = "='" & x & "[" & y & "]Sheet1'!" & are.Address 'insert a formula (link) to get the data from the closed workbook
    and changing the red text to that sheet name, leading/trailing spaces included.

    Should your first sheet names be not at all consistent, there is another way to get sheet names from a closed workbook which involves more code which I'll write if you really need it, otherwise it may be better to wait out Solution 1.

    There is copious commenting in both macros for your information and your own tweaking.
    Attached Files Attached Files
    Last edited by p45cal; 2019-09-10 at 04:24 PM.

  9. #9
    Seeker chrisl614's Avatar
    Join Date
    Apr 2019
    Posts
    12
    Articles
    0
    Excel Version
    2016
    WOW! Both Macros worked and I didn't have to make any adjustments to the code.
    I noticed that there is only 1 sheet in each workbook but the sheets are the same name as the files so they are not called "sheet1" but the macros still worked!
    Thank you!

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,597
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by chrisl614 View Post
    I didn't have to make any adjustments to the code.
    I noticed that there is only 1 sheet in each workbook but the sheets are the same name as the files so they are not called "sheet1" but the macros still worked!
    Now that's a pleasant surpise!
    I'm guessing, that when the sheet name isn't found and the dialogue would pop up asking for the correct sheet name to be chose, it simply doesn't pop up because there's only one sheet/choice!?

    Taking this a step further, there's a potential danger that if there is more than one sheet in the file, and one of them (the wrong one) is called Sheet1 the code would blithely copy from the wrong sheet.
    You can make sure that in the event of a workbook having such an arrangement (well, any workbook with more than one sheet in) the dialogue asking to choose the right sheet will popup; you only have to miss out the sheet name altogether:
    .FormulaArray = "='" & x & "[" & y & "]'!" & are.Address 'insert a formula (link) to get the data from the closed workbook
    which asks no questions if there's only one sheet.

Posting Permissions

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