宏 Excel 合并许多具有相同工作表名称的工作簿

Question

我有场景：工作簿 1、工作簿 2、工作簿 3 和工作簿 4。其中每个工作簿都有一个工作表名称wsheet_a。我将我的宏放在名为 master_file 的主工作簿中，我想合并每个工作簿中的wsheet_a工作表，以从特定行(例如第 2 行(开始mf_wsheet master_file工作簿中。一旦我重新运行宏，它就会替换mf_wsheet中的现有数据。任何帮助将不胜感激。

Answer 1

试试这个...(您需要使用工作簿的路径更新宏(

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