Merging Column from multiple workbooks to a specific range of an existing Workbook

[danng]

Hello Excel experts,

I have been struggling for two days and I really need help from you. I am fairly new to VBA coding. I hope that I can find help here. I want to be able to gather a column from multiple workbooks in the same folder to a specific range of an existing Master workbook.

A few requirements:
1. It doesn't matter where you save these files, as long as these files are in the same folder of the Master workbook, the Master will be able to capture the data.
2. It doesn't matter what file name of these files are.


What I need to transfer:
1. Range I8:I44 of Sheet1 of each file to the Master in Range I9:I45, starting column I and continue with the column J and so on
2. Range L8:L46 of Sheet2 of each file to the Master Sheet2 in Range L9:L47, starting column L and continue with the column M and so on

I tried to build up codes and I still couldn't get the data transferred to the Master.

Please help !!

 

[danng]

Just to clarify, all the excel files in the same folder have the same format.

 

[tonyyy]

You might give this a try...

Sub MergeColumns()

'''''   Place this macro workbook in the same folder as your project files

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim directory As String
Dim fileName As String

Set wb1 = ThisWorkbook
directory = ThisWorkbook.Path & "\"
fileName = Dir(directory & "*.xls?")
i = 9
j = 12

Do While fileName <> ""
    If fileName <> wb1.Name Then
        Set wb2 = Workbooks.Open(directory & fileName)
            wb2.Sheets(1).Range("I8:I44").Copy Destination:=wb1.Sheets(1).Range(Cells(9, i), Cells(45, i))
            wb2.Sheets(2).Range("L8:L46").Copy Destination:=wb1.Sheets(2).Range(Cells(9, j), Cells(47, j))
            i = i + 1
            j = j + 1
        wb2.Close savechanges:=False
    End If
    fileName = Dir
Loop

End Sub

Be sure to place the code in your Master workbook, and that your Master workbook is in the same folder as your project files.

Cheers,

tonyyy

 

[danng]

Hello Tonyyy,

Thank you so much for your reply! I put the code in my Master workbook but there is an erreur at the line

wb2.Sheets(1).Range("I8:I44").Copy Destination:=wb1.Sheets(1).Range(Cells(9, i), Cells(45, i))

I also tried this

wb2.Sheet1.Range("I8:I44").Copy Destination:=wb1.Sheet1.Range(Cells(9, i), Cells(45, i))

But it still did not work... any idea??

Thanks!

 

[tonyyy]

I may have overlooked declaring the following...

Dim i As Long
Dim j As Long

Place the above in the same grouping as the other Dim statements.

Cheers,

tonyyy

 

[danng]

Hello,

I still got the error message after adding the Dim statements ...
do you think it's missing a paste statement?

 

[tonyyy]

The Destination:= is acting as the paste function, indicating where the copied contents should be placed.

What's the error number and description?

 

[danng]

Error 1001: Error defined by application or by objet

and when I click on debug, it highlighted this line: wb2.Sheets(1).Range("I8:I44").Copy Destination:=wb1.Sheets(1).Range(Cells(9, i), Cells(45, i))

 

[tonyyy]

Thanks for the error number and description.

Delete these 2 lines:

wb2.Sheets(1).Range("I8:I44").Copy Destination:=wb1.Sheets(1).Range(Cells(9, i), Cells(45, i)) 
wb2.Sheets(2).Range("L8:L46").Copy Destination:=wb1.Sheets(2).Range(Cells(9, j), Cells(47, j))

And replace them with these

wb2.Sheets(1).Range("I8:I44").Copy Destination:=wb1.Sheets(1).Cells(9, i)
wb2.Sheets(2).Range("L8:L46").Copy Destination:=wb1.Sheets(2).Cells(9, j)

Cheers,

tonyyy

 

[danng]

Hey tonyyy,

Thank you so much!! I tried on an example and it's working great !! Although it did not work yet on my real project but I am sure I will be able to figure that out !

However, one question: I also paste the codes to "thisworkbook", but the macro does not seem to activate by itself when I open the Master. (the data is not automatically transferred, I had to go back to VB to load it) How do I get the data be updated automatically everytime I open my Master?

Thanks!