source database log clas sport set add lds
- Sub AddPovitTable()
- 'Constance
- Const DATA_SHEET As String = "Advanced Filter"
- Const DATA_ADDRESS As String = "R7C1:R107C11"
- Const PIVOT_SHEET As String = "PivotSheet" ' Sheet Name with No space
- Const PIVOT_ADDRESS As String = "R3C1"
- Const PIVOT_TABLE As String = "Pivot Table"
- 'Object
- Dim Wb As Workbook
- Dim Sht As Worksheet
- Dim pvtTable As PivotTable
- Set Wb = ThisWorkbook
- 'Do not display Alert when delete a sheet
- Application.DisplayAlerts = False
- For Each Sht In Wb.Worksheets
- If Sht.Name = PIVOT_SHEET Then Sht.Delete
- Next Sht
- Application.DisplayAlerts = True
- 'Add a new sheet for pivot table
- Set Sht = Wb.Worksheets.Add(After:=Wb.Worksheets(Wb.Worksheets.Count))
- Sht.Name = PIVOT_SHEET
- 'Create a new pivot table
- Set pvtTable = Wb.PivotCaches.Create( _
- SourceType:=xlDatabase, _
- SourceData:=DATA_SHEET & "!" & DATA_ADDRESS, _
- Version:=xlPivotTableVersion15). _
- CreatePivotTable( _
- TableDestination:=PIVOT_SHEET & "!" & PIVOT_ADDRESS, _
- TableName:=PIVOT_TABLE, _
- DefaultVersion:=xlPivotTableVersion15)
- 'set Fields
- With pvtTable
- .PivotFields("State").Orientation = xlRowField
- .PivotFields("State").Position = 1
- .PivotFields("City").Orientation = xlRowField
- .PivotFields("City").Position = 2
- .PivotFields("Salesperson").Orientation = xlRowField
- .PivotFields("Salesperson").Position = 3
- .PivotFields("Payment").Orientation = xlRowField
- .PivotFields("Payment").Position = 4
- .PivotFields("Transport").Orientation = xlRowField
- .PivotFields("Transport").Position = 5
- .PivotFields("Month").Orientation = xlRowField
- .PivotFields("Month").Position = 6
- .AddDataField .PivotFields("Product A"), "Sum:Product A", xlSum
- .AddDataField .PivotFields("Product B"), "Sum:Product B", xlSum
- .AddDataField .PivotFields("Product C"), "Sum:Product C", xlSum
- End With
- 'Release Objects
- Set Wb = Nothing
- Set Sht = Nothing
- Set pvtTable = Nothing
- End Sub
20170517xlVBA 添加数据透视表
来源: http://www.bubuko.com/infodetail-2156826.html