1、同一 EXCEL 文件合并多个工作表数据到同一工作表首先，添加通用函数 1打开 VBE。 2单击“插入模块” ，添加一个新模块。3在模块窗口，输入下面的代码。Function LastRow(sh As Worksheet)On Error Resume NextLastRow = sh.Cells.Find(what:=“*“, _After:=sh.Range(“A1“), _Lookat:=xlPart, _LookIn:=xlFormulas, _SearchOrder:=xlByRows, _SearchDirection:=xlPrevious, _MatchCase:=False
2、).RowOn Error GoTo 0End FunctionFunction LastCol(sh As Worksheet)On Error Resume NextLastCol = sh.Cells.Find(what:=“*“, _After:=sh.Range(“A1“), _Lookat:=xlPart, _LookIn:=xlFormulas, _SearchOrder:=xlByColumns, _SearchDirection:=xlPrevious, _MatchCase:=False).ColumnOn Error GoTo 0End FunctionSub 合并工作表
3、()Dim sh As WorksheetDim DestSh As WorksheetDim Last As LongDim CopyRng As RangeWith Application.ScreenUpdating = False.EnableEvents = FalseEnd With如果工作表“RDBMergeSheet“存在则将其删除Application.DisplayAlerts = FalseOn Error Resume NextActiveWorkbook.Worksheets(“RDBMergeSheet“).DeleteOn Error GoTo 0Applicat
4、ion.DisplayAlerts = True添加一个名为“RDBMergeSheet“的工作表Set DestSh = ActiveWorkbook.Worksheets.AddDestSh.Name = “RDBMergeSheet“遍历所有工作表并将数据复制到 DestShFor Each sh In ActiveWorkbook.WorksheetsIf sh.Name DestSh.Rows.Count ThenMsgBox “在工作表 Destsh 中没有足够的行用来放置数据!“GoTo ExitTheSubEnd If下面的语句从每个工作表中复制值和格式CopyRng.Copy
5、With DestSh.Cells(Last + 1, “A“).PasteSpecial xlPasteValues.PasteSpecial xlPasteFormatsApplication.CutCopyMode = FalseEnd With可选代码: 下面的语句复制工作表名称到 H 列DestSh.Cells(Last + 1, “H“).Resize(CopyRng.Rows.Count).Value = sh.NameEnd IfNextExitTheSub:Application.GoTo DestSh.Cells(1)自动调整 DestSh 工作表的列宽DestSh.Columns.AutoFitWith Application.ScreenUpdating = True.EnableEvents = TrueEnd WithEnd Sub
