发布网友 发布时间:2022-05-12 06:16
共1个回答
热心网友 时间:2023-07-29 05:15
咨询记录 · 回答于2021-11-18相同格式多个工作簿汇总在另一张表1原始数据所在工作簿包含多个格式相同的工作表,只不过每个工作表内容不同,比如说不同人名的工作表数据或者不同部门填写的数据。2在原始数据同目录下新建一个工作簿,建立两个工作表,名称分别为“首页”和“合并汇总表”。3按Alt+F11进入VBA代码编辑和调试界面。4根据提示,插入一个模块。5将下述代码粘贴到模块空白处:Sub CombineSheetsCells()Dim wsNewWorksheet As WorksheetDim cel As RangeDim DataSource, RowTitle, ColumnTitle, SourceDataRows, SourceDataColumns As VariantDim TitleRow, TitleColumn As RangeDim Num As IntegerDim DataRows As LongDataRows = 1Dim TitleArr()Dim ChoiceDim MyName$, MyFileName$, ActiveSheetName$, AddressAll$, AddressRow$, AddressColumn$, FileDir$, DataSheet$, myDelimiter$Dim n, in = 1i = 1Application.DisplayAlerts = FalseWorksheets("合并汇总表").DeleteSet wsNewWorksheet = Worksheets.Add(, after:=Worksheets(Worksheets.Count))wsNewWorksheet.Name = "合并汇总表"MyFileName = Application.GetOpenFilename("Excel工作薄 (*.xls*),*.xls*")If MyFileName = "False" ThenMsgBox "没有选择文件!请重新选择一个被合并文件!", vbInformation, "取消"ElseWorkbooks.Open Filename:=MyFileNameNum = ActiveWorkbook.Sheets.CountMyName = ActiveWorkbook.NameSet D