VBA一鍵匯總多個(gè)工作簿-名稱(chēng)相同的工作表-的指定區(qū)域數(shù)據(jù)

日常工作,，我們經(jīng)常需要匯總相同格式的工作簿的某個(gè)工作表的數(shù)據(jù)

如1月業(yè)績(jī)、2月業(yè)績(jī),。,。。,。12月業(yè)績(jī)等

VBA匯總后變成這樣：

 啥也不說(shuō)了,，直接拿代碼去用

 Dim 所有工作簿列表 As FileDialogSelectedItems
    Private Sub 提取數(shù)據(jù)按鈕_Click(sender As Object, e As EventArgs) Handles 提取數(shù)據(jù)按鈕.Click
        Dim dic As Object = CreateObject("scripting.dictionary")
        Dim wb As Excel.Workbook
        Dim sht As Excel.Worksheet
        Dim j As Long
        With App.FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogFilePicker)
            .AllowMultiSelect = True
            .Title = "可選擇多個(gè)工作簿"
            If .Show() = -1 Then
                所有工作簿列表 = .SelectedItems              '記錄所有工作簿，防止二次選擇工作簿
                For Each 工作簿路徑 As String In .SelectedItems
                    wb = App.Workbooks.Open(工作簿路徑)
                    For Each sht In wb.Worksheets
                        dic(sht.Name) = ""
                    Next
                    wb.Close(False)
                Next
                '將所有表名加載
                For Each 表名 In dic.keys
                    ComboBox2.Items.Add(表名)           '適合匯總同工作表名稱(chēng)的匯總
                Next
            End If
        End With
    End Sub
    Private Sub 匯總數(shù)據(jù)按鈕_Click(sender As Object, e As EventArgs) Handles 匯總數(shù)據(jù)按鈕.Click
        Dim wb As Excel.Workbook
        Dim sht As Excel.Worksheet
        Dim j As Long
        Dim 開(kāi)始輸出單元格 As Excel.Range = App.InputBox("請(qǐng)選擇開(kāi)始輸出單元格", Type:=8)
        Dim 輸出表 As Excel.Worksheet = App.ActiveSheet
        For Each 工作簿路徑 As String In 所有工作簿列表
            wb = App.Workbooks.Open(工作簿路徑)
            For Each sht In wb.Worksheets
                If sht.Name = ComboBox2.Text Then
                    Dim lastrow As Long = 輸出表.Cells(輸出表.Rows.Count, 開(kāi)始輸出單元格.Column).end(Microsoft.Office.Interop.Excel.XlDirection.xlUp).row + 1      '最后一行
                    sht.Range(ComboBox3.Text).Copy(輸出表.Cells(lastrow, 開(kāi)始輸出單元格.Column))      '盡量不要整列,，否則可能出錯(cuò)
                End If
            Next
            wb.Close(False)
        Next
    End Sub
    Private Sub 選擇單元格按鈕_Click(sender As Object, e As EventArgs) Handles 選擇單元格按鈕.Click
        ComboBox3.Text = App.InputBox("請(qǐng)選擇匯總區(qū)域", Type:=8).address
    End Sub

我的窗體界面是這樣的

希望大家多多支持,！謝謝