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

1，多工作表汇总（Consolidate）

‘

‘两种写法都要求地址用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 <> "汇总" Then

i = i + 1

RangeArray(i) = "'" & & "'!" & _

sht.Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

End If

Next

bk.Range("A1").Consolidate RangeArray, xlSum, True, True

[a1].Value = "姓名"

End Sub

Sub sumdemo()

Dim arr As Variant

arr = Array("一月!R1C1:R8C5", "二月!R1C1:R5C4", "三月!R1C1:R9C6") With Worksheets("汇总").Range("A1")

.Consolidate arr, xlSum, True, True

.Value = "姓名"

End With

End Sub

2，多工作簿汇总（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) = "'[" & & "]" & & "'!" & _ sht.Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)

End If

Next

Worksheets(1).Range("A1").Consolidate _

RangeArray, xlSum, True, True

End Sub

3，多工作簿汇总（）

‘2007-1-1.html###

‘help\汇总表.xls

Sub pldrwb0531()

'汇总表.xls

'导入指定文件的数据

Dim myFs As

Dim myPath As String, $

Dim i As Long, n As Long

Dim Sht1 As Worksheet, sh As Worksheet

Dim aa, nm$, nm1$, m, arr, r1, col1%

Application.ScreenUpdating = False

Set Sht1 = ActiveSheet

Set myFs = Application.

myPath = ThisWorkbook.Path

With myFs

.NewSearch

.LookIn = myPath

. = mso

. = "*.xls"

If .Execute(SortBy:=msoSortBy) > 0 Then

n = .Found

col1 = 2

ReDim myfile(1 To n) As String