Merge/join data from sheets in different files into one sheet

  • The attached file has four sheets that I have placed into one workbook to keep the file as small as possible. In reality, each sheet is in a separate file. The format of each sheet is identical. I would like to merge/join the data from the first three sheets (files) into the last sheet. If possible, it would be helpful if this could be done without having to open the first three files. Any suggestions would be greatly appreciated.

    Files

    • LanesTest.xls

      (29.18 kB, downloaded 63 times, last: )

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Merge/join data from sheets in different files into one sheet


    Hi


    I saved the three sheets with data into the same folder as the consolidation workbook and ran the following on it. Worked fine. Just make sure your variables are correct for your set up.


    Take care


    Smallman


  • Re: Merge/join data from sheets in different files into one sheet


    Thank you very much Smallman. I would like to have all the data copied to column B in the Consolodation worksheet in Range("B1:B8"). Would that be possible? Is "book" with the variable "i" the file name? This would mean that I would replace "book" with the file name that I am using. Would that be correct? I apologize that I wasn't clear in my original posting. Thank you again.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Merge/join data from sheets in different files into one sheet


    I would go for this procedure, based on the work of Smallman, but with a number of own additions.


    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  • Re: Merge/join data from sheets in different files into one sheet


    Hi Mumps


    In answer;


    Quote

    Would that be possible? Is "book" with the variable "i" the file name? This would mean that I would replace "book" with the file name that I am using.


    to the above. You are correct. Whether you use wigi's solution or mine the premise is the same. Just change the book name to your generic file name. I would keep the file names similar. If the file names are not tagged with a number the way we have assumed. I would put the file names in an array and call each in turn as you are refering to each file.


    Something like this;


    Code
    1. Dim ar As Variant
    2. ar = Array("Revenue", "Expence", "Capital")


    now


    Code
    1. fl = ar(i) & ".xlsx"


    This will get around this issue.


    Take care


    Smallman

  • Re: Merge/join data from sheets in different files into one sheet


    Smallman and Wigi, thank you so very much for your help. I ran Wigi's macro and it worked well except that it copied the data from the first three sheets into sheet1. The macro should leave the first three sheets unchanged and the data from each should be copied to sheet4.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Merge/join data from sheets in different files into one sheet


    Hello again. I have been working with your advice with the actual data and I think that I'm almost there. I have attached a file with the real data. I would like to copy the data from the first 3 sheets to the RegionLanes sheet. I ran the following version of the macro you sent me. It appears to copy the data from the first sheet and then I get an error (subscript out of range). I'm not sure how to fix the problem. I'm still learning VBA and I appreciate your help and patience.


    [VB]
    Sub ImportFormula()

    rng = "A1:G238"
    fold = ThisWorkbook.Path & "\"

    With Range(rng)
    sq = .Value
    For i = 1 To 3
    sh = "Sheet" & i
    fl = "book" & i & ".xlsx"
    fmula = "'" & fold & "[" & fl & "]" & sh & "'!" & Split(.Address(0, 0), ":")(0)
    .Formula = "=if(" & fmula & "="""",""""," & fmula & ")"
    For ii = 1 To .Count
    If Len(.Cells(ii)) Then sq(ii, 1) = .Cells(ii)
    Next
    Next
    .Value = sq
    End With

    End Sub[/VB]

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.