Sub Tong_Hop()
Dim ObjFile As Object, Res(), Source As String, sh As Worksheet, sArr()
Dim LastC As Long, i As Long, k As Long, j As Long, Dic As Object, nam As Long, cot As Long
Set Dic = CreateObject("scripting.dictionary")
For i = 1 To 20
Dic(i + 2009) = i
Next
Sheet1.[A2].Resize(10000, 20).ClearContents
Sheet2.[A2].Resize(10000, 20).ClearContents
With CreateObject("Scripting.FileSystemObject")
With .GetFolder(ThisWorkbook.Path)
For Each ObjFile In .Files
If ObjFile.Name <> ThisWorkbook.Name Then
If Left(ObjFile.Name, 1) <> "~" Then
Source = ThisWorkbook.Path & "\" & ObjFile.Name
With Workbooks.Open(Source, 0)
For Each sh In .Worksheets
ReDim Res(1 To 100, 1 To 22)
LastC = sh.[Z11].End(1).Column
sArr = sh.Range("A11", sh.[A65536].End(3)).Resize(, LastC).Value
For i = 2 To UBound(sArr) - 9
For j = 2 To UBound(sArr, 2)
nam = sArr(1, j)
If Dic.exists(nam) Then
cot = Dic.Item(nam)
Res(i, 1) = Split(ObjFile.Name, ".")(0)
Res(i, 2) = sArr(i, 1)
Res(i, cot + 2) = sArr(i, j)
End If
Next
Next
If sh.Name Like "L*" Then
Sheet1.[A65536].End(3).Offset(1).Resize(i, UBound(Res, 2)) = Res
Else
Sheet2.[A65536].End(3).Offset(1).Resize(i, UBound(Res, 2)) = Res
End If
Next
.Close False
End With
End If
End If
Next
End With
End With
End Sub