照例举个栗子,如下图,一个工作簿,包含多个工作表的格式都是相同,现在需要将各个分表的数据汇总到总表中,如果是你,你会怎么去做? Sub CollectSheets() Dim sht As Worksheet, rng As Range, k&, trow&, temp Application.ScreenUpdating = False temp = InputBox('请输入需要合并的工作表所包含的关键词:', '提醒') If StrPtr(temp) = 0 Then Exit Sub trow = Val(InputBox('请输入标题的行数', '提醒')) If trow < 0 Then MsgBox '标题行数不能为负数。', 64, '警告': Exit Sub Cells.ClearContents For Each sht In Worksheets If sht.Name <> ActiveSheet.Name Then If InStr(1, sht.Name, temp, vbTextCompare) Then Set rng = sht.UsedRange k = k + 1 If k = 1 Then rng.Copy [a1].PasteSpecial Paste:=xlPasteValues Else rng.Offset(trow).Copy Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1).PasteSpecial Paste:=xlPasteValues End If End If End If Next Range('a1').Activate MsgBox '您汇总了' & k & '个工作表' Application.ScreenUpdating = True End Sub
|
|