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.
Merge/join data from sheets in different files into one sheet
-
-
-
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
Code
Display MoreOption Explicit Sub ImportFormula() Dim fold As String Dim sh As String Dim fl As String Dim rng As String Dim fmula As String Dim toRng As Range Dim i As Integer fold = ThisWorkbook.Path & "\" sh = "Consolidation" For i = 1 To 3 fl = "book" & i & ".xlsx" rng = "B1:B8" fmula = "='" & fold & "[" & fl & "]" & sh & "'!" fmula = fmula & Split(rng, ":")(0) Set toRng = Cells(1, i + 1) With Range(rng) toRng.Resize(.Rows.Count, .Columns.Count).Formula = fmula End With Next i End Sub
-
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.
-
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.
Code
Display MoreSub ImportFormula() rng = "B1:B8" 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
-
Re: Merge/join data from sheets in different files into one sheet
Hi Mumps
In answer;
QuoteWould 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;
now
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.
-
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]
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!