Results 1 to 3 of 3

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

  1. #1
    Junior Member
    Join Date
    Jun 2015
    Posts
    1
    Rep Power
    0

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

    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

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    Hi

    Welcome to board !!

    Put this code in the master workbook.

    Code:
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Feb 2019
    Posts
    1
    Rep Power
    0
    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!

Similar Threads

  1. Merge Workbooks to Master Workbook
    By donb1337 in forum Excel Help
    Replies: 6
    Last Post: 09-26-2013, 09:16 PM
  2. Replies: 1
    Last Post: 09-21-2013, 11:28 AM
  3. Replies: 4
    Last Post: 06-18-2013, 01:38 PM
  4. Replies: 2
    Last Post: 05-28-2013, 05:32 PM
  5. Replies: 3
    Last Post: 05-14-2013, 03:25 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •