|
- Sub test1()
- Application.ScreenUpdating = False
- Dim aw, t, k, br, s
- Dim rg As Range, wk As Workbook
- Set aw = ThisWorkbook
- '以下遍历文件,提取各公司数据
- p = aw.Path & ""
- f = Dir(p & "*.xls")
- Do While f <> ""
- If f <> ThisWorkbook.Name Then
- Set wk = GetObject(p & f)
- wk.Sheets(1).Activate
- t = InStr(wk.Name, ".")
- s = Left(wk.Name, t - 1)
- aw.Activate
- q = ActiveSheet.Name
- wk.Sheets(1).Activate
- Set rg = wk.Sheets(1).[4:4].Find(q)
- If Not rg Is Nothing Then
- k = rg.Column
- br = wk.Sheets(1).Range(Cells(6, k), Cells(131, k + 1)).Value
- Else
- MsgBox ActiveSheet.Name & "的数据未找到!"
- End If
- aw.Activate
- Set rg = ActiveSheet.[3:3].Find(s)
- If Not rg Is Nothing Then
- k = rg.Column
- ActiveSheet.Range(Cells(5, k), Cells(130, k + 1)) = br
- Else
- MsgBox ActiveSheet.Name & "的数据未找到!"
- End If
- wk.Close False
- End If
- f = Dir
- Loop
- Application.ScreenUpdating = True
- MsgBox "数据汇总完毕!"
- End Sub
复制代码
打开总执行表,按汇总按钮即可。其他表不用打开。
分公司月汇总.rar
(465.3 KB, 下载次数: 5)
|
|