|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 autolzg 于 2019-3-13 13:53 编辑
- Sub 一键汇总()
- Call 按业务员汇总
- Dim d As Object, sumx, i%, j%, k, rowx
- Dim sht As Worksheet
- Set d = CreateObject("Scripting.Dictionary")
-
- For Each sht In Worksheets
- If sht.Name <> "各月销售汇总" Then
-
- k = Cells(1000, 1).End(3).Row + 1
-
- For i = 1 To 12
- sumx = 0
- For j = 1 To k
- sumx = sumx + sht.Cells(j + 1, i + 1) 'N列
- Next j
- d(i) = d(i) + sumx
-
- Next i
- Debug.Print d.Count
-
- With ThisWorkbook.Worksheets("各月销售汇总")
- rowx = .Cells(19, 1).End(3).Row + 1
- .Cells(rowx, 1) = sht.Name
- For i = 1 To 12
- .Cells(rowx, i + 1) = d(i)
- Next i
- End With
- d.RemoveAll
- End If
- Next sht
- End Sub
- Sub 按业务员汇总()
-
- Dim d As Object, Mth, countx, RowY%
- Dim i%, j%, lj$, FN$, nm$
- Dim sh As Worksheet
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("Scripting.Dictionary")
- lj = ActiveWorkbook.Path
- nm = ActiveWorkbook.Name
-
- For Each sh In Worksheets
- If sh.Name <> "各月销售汇总" Then
- FN = Dir(lj & "\" & sh.Name & "\*.xls*")
-
- Do While FN <> ""
- Workbooks.Open filename:=lj & "\" & sh.Name & "\" & FN '打开要汇总的工作簿
-
- With Workbooks(FN).Worksheets("sheet1")
- j = 3
- Do While Range("C" & j + 1).Value <> ""
- j = j + 1
- Loop
- For i = 4 To j
- Mth = Month(Cells(i, 3))
- countx = Cells(i, 14) 'N列
- d(Mth) = d(Mth) + countx
- Next i
- End With
-
- Workbooks(FN).Close True
-
- With Workbooks(nm).Worksheets(sh.Name)
- RowY = .Cells(10000, 1).End(3).Row + 1
- .Cells(RowY, 1) = Left(FN, Len(FN) - 4)
- For i = 1 To 12
- .Cells(RowY, i + 1) = d(i)
- Next i
- End With
-
- d.RemoveAll
-
- FN = Dir
- Loop
-
- End If
- Next sh
-
- Application.ScreenUpdating = True
- Application.DisplayAlerts = True
-
- End Sub
复制代码
未经严格测试
客户0000.rar
(101.64 KB, 下载次数: 3)
|
|