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-...features/merge

Cheers!