VBA macro/button to copy data from multiple workbooks into master workbook

[darkwizdom42]

Hi Everyone!

I was wondering if someone could please help me to create a VBA code for the following situation. I'm really new to VBA and not sure if it would be possible. Any help would be greatly appreciated!

I have to copy all data from about 12 workbooks and paste it into a master workbook.

All the workbooks have a tab with data and the default "sheet1" name. The master workbook has multiple tabs with each tab having the name of the report/workbook. I have to open each workbook, select all the data and then paste into it's designated tab in the master worksheet.

Is there any way to make a macro button so it automatically copies all the data from each workbook into the master workbook?

All the workbooks are located in the same folder. The Master workbook is located just outside the Reports folder, where all the workbooks are.

Please let me know if you require any clarification.

Thank you!

 

[John_w]

Try this macro. You need to change the matchWorkbooks string, where indicated, to specify the folder path and wildcard file name of the workbooks.

Option Explicit

Public Sub Copy_Workbooks_Sheet_To_Master_Workbook()

    Dim masterWb As Workbook
    Dim matchWorkbooks As String, folderPath As String, wbFileName As String
    Dim sourceWb As Workbook
    Dim sourceRange As Range
    Dim destWs As Worksheet
    
    Set masterWb = ThisWorkbook
    
    'Folder path and wildcard workbook file names whose "sheet1" are to be copied and pasted to the sheet matching the workbook file name in Master workbook
    
    matchWorkbooks = "C:\path\to\workbooks\*.xls*"                                'CHANGE THIS
   
    Application.ScreenUpdating = False
            
    folderPath = Left(matchWorkbooks, InStrRev(matchWorkbooks, "\"))
    wbFileName = Dir(matchWorkbooks)
    
    While wbFileName <> vbNullString
    
        Set sourceWb = Workbooks.Open(folderPath & wbFileName)
        
        Set sourceRange = sourceWb.Worksheets(1).UsedRange
        
        Set destWs = masterWb.Worksheets(Left(sourceWb.Name, InStrRev(sourceWb.Name, ".") - 1))
        destWs.Cells.Delete
        destWs.Activate
        
        sourceRange.Copy
        
        With destWs.Range("A1")
            .PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            'Use Format Painter to copy and paste row heights
            sourceRange.EntireRow.Copy
            .Resize(sourceRange.Rows.Count).EntireRow.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Select
        End With
        
        Application.CutCopyMode = False
        sourceWb.Close SaveChanges:=False
        DoEvents
        wbFileName = Dir
        
    Wend
    
    Application.ScreenUpdating = True
    
    MsgBox "Finished"
    
End Sub

 

[darkwizdom42]

Thank you so much! Worked perfectly and copied all the data from all the workbooks.

Just had one concern. After I ran the macro, the formulas I have on some of the other tabs that used the copied data (other tabs not used in the macro) suddenly got the #REF! errors after the macro finished. Is there any way to correct this? If I manually correct the #REF! errors in the formulas after, It pulls the data like normal.

Once again, thanks for your help!

 

[John_w]

After I ran the macro, the formulas I have on some of the other tabs that used the copied data (other tabs not used in the macro) suddenly got the #REF! errors after the macro finished. Is there any way to correct this?

Replace:

        destWs.Cells.Delete

with:

        destWs.Cells.Clear