|
- Sub yy()
- Application.ScreenUpdating = False
- Dim aw As Workbook, k%, rg As Range, s$, r$
- Dim arr, m%, rw%
- Set aw = ThisWorkbook
- aw.Sheets("汇总表").[A:c] = ""
- aw.Sheets("汇总表").Range(Cells(1, 1), Cells(1, 3)) = Array("表名", "名字1", "名字2")
-
- '以下遍历文件,提取各单数据
- p = aw.Path & ""
- f = Dir(p & "*.xls")
- Do While f <> ""
- If f <> ThisWorkbook.Name Then
- Set wk = GetObject(p & f)
- wk.Sheets("4").Activate
- Set rg = wk.Sheets("4").[a:a].Find("名字1")
- k = rg.CurrentRegion.Rows.Count
- arr = rg.Offset(1, 1).Resize(k - rg.Row + 1, 2)
- rw = aw.Sheets("汇总表").[b65536].End(3).Row
- r = wk.Name
- w = InStr(wk.Name, ".")
- s = Left(wk.Name, w - 1)
- MsgBox s
- aw.Sheets("汇总表").Range("a" & rw + 1 & ":a" & UBound(arr) + rw).Value = s
- aw.Sheets("汇总表").Range("b" & rw + 1).Resize(UBound(arr), 2) = arr
- wk.Close False
- End If
- f = Dir
- Loop
- m = aw.Sheets("汇总表").[b65536].End(3).Row
-
- With aw.Sheets("汇总表").Range(Cells(1, 1), Cells(m, 3)).Borders
- .LineStyle = xlContinuous
- .ColorIndex = 0
- .TintAndShade = 0
- .Weight = xlThin
- End With
- aw.Sheets("汇总表").[A:c].Columns.AutoFit
-
- Application.ScreenUpdating = True
- MsgBox "汇总完毕!"
- End Sub
多表格数据汇总-急急急.rar
(230.74 KB, 下载次数: 11)
|
|