'按项目创建
Sub AddSheet()
Dim sh, pts, i
Application.ScreenUpdating = False
Call DelSheet
Set sh = Sheets(2)
Set pts = sh.PivotTables(1).PivotFields("BUYER").PivotItems
For i = 1 To pts.Count
Call FilterItem(i)
Sheets.Add(after:=Sheets(Sheets.Count)).Name = pts(i)
sh.Range("e6").CurrentRegion.Copy [c10]
Next
sh.Activate
End Sub
'清除工作表
Private Sub DelSheet()
Dim i
Application.DisplayAlerts = False
For i = Sheets.Count To 3 Step -1
Sheets(i).Delete
Next i
End Sub
'筛选项目
Sub FilterItem(j)
Dim pf, i
Set pf = Sheets(2).PivotTables(1).PivotFields("BUYER")
pf.ClearAllFilters
For i = 1 To pf.PivotItems.Count
pf.PivotItems(i).Visible = i = j
Next
End Sub 透视表自动分表 VBA2.rar(42.1 KB, 下载次数: 5)