VBA to Merge Multiple Workbooks with Multiple sheets into one master sheet

[Moe Kadhom]

Hi
I was wondering If you can help me with my problem.

I have 17 files that have 9 sheets each. I have one workbook that has one sheet in it called master.

I want to run a macro from the master sheet that copies range A1-B50 from each sheet in the 17 files and paste them as values one after the other. As this is a master sheet, Next time I run the macro, I want the data to be pasted on the next empty column. so the first time macro is ran, data be pasted in column A and B, second time C and D.......

Please note:
-Not sure if this matters, but sheets are password protected
-every month the location of the folder containing the 17 files changes so it would be great if I can choose the files.

Your help with this is much appreciated and if this works properly, I will save myself hours of work every month.

thanks

[Admin]

Hi

Welcome to board !!

Put this code in the master workbook.


Option Explicit

Sub kTest()

Dim AllFiles() As String, i As Long, fn As String, r As Range
Dim Wbk As Workbook, WkSht As Worksheet, lc As Long


Const ShtPassword As String = "pwd" '<<< change the password here

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Filters.Add "Excel Files", "*.xls*"
If .Show Then
ReDim AllFiles(1 To .SelectedItems.Count)
For i = 1 To .SelectedItems.Count
AllFiles(i) = .SelectedItems(i)
Next
Else
Exit Sub
End If
End With
Application.ScreenUpdating = 0
With ThisWorkbook
fn = .FullName
With .Worksheets(1)
lc = .Cells(1).CurrentRegion.Columns.Count
lc = IIf(lc = 1, 1, lc + 1)
Set r = .Cells(2, lc)
End With
End With

For i = LBound(AllFiles) To UBound(AllFiles)
If Not fn = AllFiles(i) Then
Set Wbk = Workbooks.Open(AllFiles(i), 0)
For Each WkSht In Wbk.Worksheets
WkSht.Unprotect ShtPassword
r.Resize(50, 2).Value = WkSht.Range("a1:b50").Value
Set r = r.Offset(50)
Next
Wbk.Close 0
Set Wbk = Nothing
End If
Next
Application.ScreenUpdating = 1

End Sub

adjust the sheet password in the code itself.

[synkronizer]

Hi, I know you are searching VBA Code here. But I want to share a simple tool which can help you to merge multiple Workbooks without writing any single code.
Check it out: https://www.synkronizer.com/compare-excel-tables-features/merge

Cheers!