VBA一键汇总多个工作簿-名称相同的工作表-的指定区域数据
日常工作，我们经常需要汇总相同格式的工作簿的某个工作表的数据
如1月业绩、2月业绩。。。。12月业绩等
VBA汇总后变成这样：
啥也不说了，直接拿代码去用
Dim 所有工作簿列表 As FileDialogSelectedItemsPrivate Sub 提取数据按钮_Click(sender As Object, e As EventArgs) Handles 提取数据按钮.ClickDim dic As Object = CreateObject("scripting.dictionary")Dim wb As Excel.WorkbookDim sht As Excel.WorksheetDim j As LongWith App.FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogFilePicker).AllowMultiSelect = True.Title = "可选择多个工作簿"If .Show() = -1 Then所有工作簿列表 = .SelectedItems '记录所有工作簿，防止二次选择工作簿For Each 工作簿路径 As String In .SelectedItemswb = App.Workbooks.Open(工作簿路径)For Each sht In wb.Worksheetsdic(sht.Name) = ""Nextwb.Close(False)Next'将所有表名加载For Each 表名 In dic.keysComboBox2.Items.Add(表名) '适合汇总同工作表名称的汇总NextEnd IfEnd WithEnd SubPrivate Sub 汇总数据按钮_Click(sender As Object, e As EventArgs) Handles 汇总数据按钮.ClickDim wb As Excel.WorkbookDim sht As Excel.WorksheetDim j As LongDim 开始输出单元格 As Excel.Range = App.InputBox("请选择开始输出单元格", Type:=8)Dim 输出表 As Excel.Worksheet = App.ActiveSheetFor Each 工作簿路径 As String In 所有工作簿列表wb = App.Workbooks.Open(工作簿路径)For Each sht In wb.WorksheetsIf sht.Name = ComboBox2.Text ThenDim lastrow As Long = 输出表.Cells(输出表.Rows.Count, 开始输出单元格.Column).end(Microsoft.Office.Interop.Excel.XlDirection.xlUp).row + 1'最后一行sht.Range(ComboBox3.Text).Copy(输出表.Cells(lastrow, 开始输出单元格.Column))'尽量不要整列，否则可能出错End IfNextwb.Close(False)NextEnd SubPrivate Sub 选择单元格按钮_Click(sender As Object, e As EventArgs) Handles 选择单元格按钮.ClickComboBox3.Text = App.InputBox("请选择汇总区域", Type:=8).addressEnd Sub
我的窗体界面是这样的
希望大家多多支持！谢谢
