Excel-VBA-多工作簿多工作表汇总实例集锦

1,多工作表汇总（Consolidate）‘http://www。excelpx.com/dispbbs。asp?boardID=5＆ID=110630&page=1‘两种写法都要求地址用 R1C1 形式，各个表格的数据布置有规定。Sub ConsolidateWorkbook(） Dim RangeArray(） As String Dim bk As Worksheet Dim sht As Worksheet Dim WbCount As Integer Set bk = Sheets("汇总”） WbCount = Sheets.Count ReDim RangeArray(1 To WbCount - 1） For Each sht In Sheets If sht。Name <> ”汇总" Then i = i + 1 RangeArray（i） = ”'" ＆ sht.Name & "’!” ＆ _ sht。Range（"A1”）。CurrentRegion.Address（ReferenceStyle:=xlR1C1) End If Next bk。Range(”A1”)。Consolidate RangeArray, xlSum， True, True ［a1]。Value = ”姓名" End SubSub sumdemo（)Dim arr As Variant arr = Array（”一月!R1C1：R8C5"， ”二月!R1C1：R5C4”， "三月！R1C1:R9C6”) With Worksheets（"汇总").Range("A1”） .Consolidate arr， xlSum， True, True 。Value = ”姓名” End WithEnd Sub2，多工作簿汇总(Consolidate）‘多工作簿汇总Sub ConsolidateWorkbook（) Dim RangeArray() As String Dim bk As Workbook Dim sht As Worksheet Dim WbCount As Integer WbCount = Workbooks.Count ReDim RangeArray（1 To WbCount - 1） For Each bk In Workbooks '在所有工作簿中循环 If Not bk Is ThisWorkbook Then ’非代码所在工作簿 Set sht = bk.Worksheets（1) ’引用工作簿的第一个工作表 i = i + 1 RangeArray（i) = ”’[" & bk。Name ＆ ”］" ＆ sht。Name ＆ "'!" ＆ _ sht.Range（”A1"）.CurrentRegion.Address（ReferenceStyle：=xlR1C1) End If Next Worksheets（1).Range("A1”）。Consolidate _ RangeArray， xlSum， True， TrueEnd Sub3,多工作簿汇总（FileSearch)‘http://club.excelhome.net/thread-442007—1—1。html＃＃#‘help\汇总表.xlsSub pldrwb0531()'汇总表.xls'导入指定文件的数据 Dim myFs As FileSearch Dim myPath As String, Filename$ Dim i As Long, n As Long Dim Sht1 As Worksheet, sh As Worksheet Dim aa， nm＄, nm1＄, m, arr， r1, col1％Application.Sc...