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]

Also, when the worksheet is protected, the VBC is not working...

 

[tonyyy]

You need to change the first line from

Sub MergeColumns()

to

Private Sub Workbook_Open()

Cheers,

tonyyy

 

[tonyyy]

Suggest you post another request for the protected worksheet. The item wasn't in the original scope, and I don't know when I'd be able to get to it.

tonyyy

 

[danng]

I solved the problem to run the macro automatically when open the Master file.

I also added these codes at the opening and the closing, but still the VB get stucked because the Master sheet is protected... What is the error there? Please help!


Sub UnprotectAll()
Dim sh As Worksheet
Dim yourPassword As String
yourPassword = "cheval"

For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect Password:=yourPassword
Next sh

End Sub


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
Dim i As Long
Dim j As Long


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("i7:i43").Copy Destination:=wb1.Sheets(1).Cells(7, i)

i = i + 1
j = j + 1
wb2.Close savechanges:=False
End If
fileName = Dir
Loop

End Sub

Sub ProtectAll()
Dim sh As Worksheet
Dim yourPassword As String
yourPassword = "cheval"

For Each sh In ActiveWorkbook.Worksheets
sh.Protect Password:=yourPassword
Next sh

End Sub

 

[danng]

Thank you Tonyyy for your help!!
I figured out how to run the VB in the protected sheet. Thank you so much again for your great help!! Cheers!