- Sub Macro1()
- Dim wb As Workbook, mypath$, wj$, i&, s&, s2&, n%
- Dim arr, brr(1 To 60000, 1 To 9), crr(1 To 60000, 1 To 9)
- Application.ScreenUpdating = False
- tj1 = [b1]: tj2 = [b2]
- mypath = ThisWorkbook.Path & ""
- wj = Dir(mypath & "*.xls")
- Do While wj <> ""
- If wj <> ThisWorkbook.Name Then
- Set wb = GetObject(mypath & wj)
- gzb = Split(wb.Name, ".")(0) '工作簿名称
- For i = 1 To wb.Sheets.Count
- sht = wb.Sheets(i).Name '工作表名称
- arr = wb.Sheets(i).Range("a1:ah" & wb.Sheets(i).Range("c65536").End(xlUp).Row)
- For j = 6 To UBound(arr) - 1
- If arr(j, 16) > tj1 Then
- s = s + 1
- brr(s, 1) = arr(j, 16)
- brr(s, 2) = gzb
- brr(s, 3) = sht
- n = 4
- For k = 25 To UBound(arr, 2)
- If arr(j + 1, k) = "" Then
- n = n + 1
- brr(s, n) = arr(1, k)
- End If
- Next
- End If
- If arr(j, 17) > tj2 Then
- s2 = s2 + 1
- crr(s2, 1) = arr(j, 17)
- crr(s2, 2) = gzb
- crr(s2, 3) = sht
- crr(s2, 5) = arr(j + 1, 3)
- crr(s2, 6) = arr(j + 1, 13)
- crr(s2, 7) = arr(j + 1, 14)
- End If
- Next
- Next
- wb.Close 0
- End If
- wj = Dir
- Loop
- Sheet2.Range("a2").Resize(s, UBound(brr, 2)) = brr
- Sheet3.Range("a2").Resize(s2, UBound(crr, 2)) = crr
- Application.ScreenUpdating = True
- End Sub
复制代码 |