Sub RunSubtotal()
Dim c
c = 1 '分类列的列号
With Sheets("原材料")
'1)删除分类汇总
.Range("a1").CurrentRegion.RemoveSubtotal
'2)对要分类的字段,排升序
.Cells(1, c).CurrentRegion.Sort key1:=.Cells(1, c), order1:=xlAscending, Header:=xlYes
'3)以第一个字段进行分组的求和,汇总对象为第四到第七个字段。
.Range("a1").CurrentRegion.Subtotal GroupBy:=c, Function:=xlSum, TotalList:=Array(4, 5, 6, 7)
'.Outline.ShowLevels RowLevels:=3
.Columns(c).Replace "汇总", "累计"
.Rows(.Range("a1").CurrentRegion.Rows.Count).Delete
.Range("a1").CurrentRegion.Borders.LineStyle = 1
End With
Call PrintBySubtotal(c)
End Sub
Sub PrintBySubtotal(c)
Dim A, i, r
Application.DisplayAlerts = False
With Sheets.Add(after:=Sheets(Sheets.Count))
.PageSetup.Orientation = xlLandscape
.PageSetup.PrintTitleRows = "$1:$1"
Sheets("原材料").[a1:i1].Copy .[a1]
r = 2
A = Sheets("原材料").Range("a1").CurrentRegion
For i = 2 To UBound(A)
If A(i, c) Like "* 累计" Then
.Rows("2:65536").Clear
Sheets("原材料").Cells(r, 1).Resize(i - r + 1, 9).Copy
r = i + 1
.Range("a2").PasteSpecial xlPasteFormats
.Range("a2").PasteSpecial xlPasteValues
.Cells.Columns.AutoFit
.PageSetup.CenterHeader = .[A2]
.PrintOut
End If
Next i
.Delete
End With
End Sub
原材料3.rar
(51.14 KB, 下载次数: 23)