Macro Excel Consolidate Many Workbooks with Same Worksheet Name

Question

I have scenario: Workbook1, Workbook2, Workbook3, and Workbook4. Each of these workbooks have a sheet name wsheet_a. I am putting my macro in master workbook named master_file and I wanted to consolidate the wsheet_a worksheets in each workbook to mf_wsheet in master_file workbook starting at a specific row (e.g.row 2). And once I rerun the macro, it replace the the existing data in mf_wsheet. Any help is greatly appreciated.

have you written any code yet? You will want to start with looping through all the workbooks to find the sheet. something like. Dim wb As Workbook, ws As Worksheet For Each wb in Application.Workbooks For Each ws in wb.Worksheets If ws.name = "wsheet_a" Then 'do what you need to do End If Next ws Next wb — excelledsoftware

Josh Josh · Accepted Answer · 2017-02-03T03:06:06

Try this... (You will need to update the macro with the path to your workbooks)

Option Explicit

Sub Compile_Workbook_Data()

    Dim master_wkbk As Workbook: Set master_wkbk = ThisWorkbook
    Dim master_sht As Worksheet: Set master_sht = ThisWorkbook.Worksheets("mf_wsheet")
    Dim current_wkbk As Workbook
    Dim current_sht As Worksheet
    Dim wkbk_list(1 To 4) As String
    Dim x As Integer
    Dim last_row As Integer
    Dim last_col As Integer

    wkbk_list(1) = "Workbook1.xlsx"
    wkbk_list(2) = "Workbook2.xlsx"
    wkbk_list(3) = "Workbook3.xlsx"
    wkbk_list(4) = "Workbook4.xlsx"

    For x = 1 To UBound(wkbk_list)

        Set current_wkbk = Workbooks.Open("Full Path to File" & wkbk_list(x))
        Set current_sht = current_wkbk.Worksheets("wsheet_a")

        last_row = current_sht.Cells.Find("*", searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
        last_col = current_sht.Cells.Find("*", searchorder:=xlByColumns, SearchDirection:=xlPrevious).Column

        current_sht.Range(Cells(1, 1), Cells(last_row, last_col)).Copy

        last_row = master_sht.Cells.Find("*", searchorder:=xlByRows, SearchDirection:=xlPrevious).Row
        master_sht.Range("A" & last_row + 1).PasteSpecial Paste:=xlPasteValues

        current_wkbk.Close False

    Next x

End Sub

Macro Excel Consolidate Many Workbooks with Same Worksheet Name

1 Answers