|
发表于 2014-4-2 18:24
|
显示全部楼层
本楼为最佳答案
- Sub Macro1()
- Dim arr, brr, wb As Workbook, mypath$, wj$, i&
- arr = [c3:ak4] '如果数据有规律,brr可循环赋值,否则逐条添加
- ReDim brr(1 To 20000, 1 To UBound(arr, 2))
- Application.ScreenUpdating = False
- mypath = ThisWorkbook.Path & ""
- wj = Dir(mypath & "*.xls*")
- Do While wj <> ""
- If wj <> ThisWorkbook.Name Then
- Set wb = GetObject(mypath & wj)
- For i = 2 To wb.Sheets.Count - 2
- s = s + 1
- brr(s, 1) = wb.Name
- brr(s, 2) = wb.Sheets(i).Name
- With wb.Sheets(i)
- brr(s, 3) = .[ar55]
- brr(s, 4) = .[au2]
- brr(s, 5) = .[au4]
- '以下类推添加
- End With
- Next
- wb.Close 0
- End If
- wj = Dir
- Loop
- Range("c5").Resize(s, UBound(brr, 2)) = brr
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|