[VBA] summarizes the specified worksheets in multiple workbooks into a specified worksheet in the same workbook

Sub Rollup multiple workbooks ()

application.screenupdating = False

Dim WB as Workbook, F as String, l as String, n as String, m as String, J as Integer

F = thisworkbook.path & "\"

L = f & "*.xls"

m = Dir (L)

Do and M <> ""

If m <> Thisworkbook.name Then

n = f & M

Workbooks.Open (N)

With Thisworkbook.activesheet

. Range ("B4:at34"). ClearContents

For i = 4 to. Range ("A1"). CurrentRegion.Rows.Count

For j = 2 to. Range ("A1"). Currentregion.columns.count-2 Step 3

For each WB in Workbooks

If WB. Name <> Thisworkbook.name Then

AA = Left (wb. Name, InStrRev (WB. Name, ".") -1)

If. Cells (2, J). Value = AA Then

. Cells (i, j) = Application.vlookup (. Cells (i, 1), WB. Worksheets (1). Range ("A:b"), 2, 0)

. Cells (I, j + 1) = Application.vlookup (. Cells (i, 1), WB. Worksheets (1). Range ("A:c"), 3, 0)

If VBA. IsNumeric (ThisWorkbook.activesheet.Cells (i, J + 1)) = False Then

ThisWorkbook.activesheet.Cells (i, j + 2) = 0

ElseIf ThisWorkbook.activesheet.Cells (I, j + 1) = 0 Then

ThisWorkbook.activesheet.Cells (i, j + 2) = 0

Else

ThisWorkbook.activesheet.Cells (i, j + 2) = ThisWorkbook.activesheet.Cells (i, J)/ThisWorkbook.activesheet.Cells (I, j + 1 )

End If

End If

End If

Next

Next

Next

End with

End If

m = Dir

Loop

For each WB in Workbooks

If WB. Name <> Thisworkbook.name Then

Wb. Close False

End If

Next

application.screenupdating = True

End Sub

：

Insufficient:

Call the Excel itself function VLOOKUP, the large amount of data will result in slow running, table stuck problem, post-optimization, apply array resolution.

[VBA] summarizes the specified worksheets in multiple workbooks into a specified worksheet in the same workbook