Option Explicit
Sub test()
Dim arr, brr, mark, i, j, k, n, dic
ReDim arr(1 To 100, 1 To 3 * 20 * 6)
Set dic = CreateObject("scripting.dictionary")
For i = 1 To 3
brr = Sheets("sheet" & i).[a1].CurrentRegion.Value
For j = 1 To UBound(brr, 1)
For k = 1 To UBound(brr, 2)
arr(j, k + n) = brr(j, k)
If Len(arr(1, k + n)) Then dic(arr(1, k + n)) = k + n
Next
Next
n = n + UBound(brr, 2)
Next
With Sheets("sheet4")
mark = .[h1:m1].Value
ReDim brr(1 To UBound(arr, 1), 1 To UBound(mark, 2) * 6)
For i = 1 To UBound(mark, 2)
If dic.exists(mark(1, i)) Then
n = dic(mark(1, i))
For j = 1 To UBound(arr, 1)
For k = n - 5 To n
brr(j, (i - 1) * 6 + k - (n - 5) + 1) = arr(j, k)
Next
Next
End If
Next
.[o1].Resize(UBound(brr, 1), UBound(brr, 2)) = brr
End With
End Sub |