1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| Sub PivotTablenew()
Rem -----information----
Rem Delete the existing pivot Tables
Application.DisplayAlerts = False
Rem Declare variable
Dim PvtTbl As PivotTable
Dim WsData As Worksheet
Dim wspvtTbl As Worksheet
Dim rngData As Range
Dim LastRow As Integer
With Sheets("main work centers")
LastRow = .Cells(.Rows.Count, "G").End(xlUp).Row
End With
Rem determine which worksheet contains the data source --Dim WsData As Worksheet--
Set WsData = Worksheets("main work centers")
Rem determine where the pivot table will be created --Dim wspvtTbl As Worksheet--
Set wspvtTbl = Worksheets("pivot")
Rem delete all pivotables in the worksheet
For Each PvtTbl In wspvtTbl.PivotTables
PvtTbl.ClearTable
Next PvtTbl
Application.DisplayAlerts = True
Rem Set source data range
rngData = WsData.Range("G15:AB" & LastRow)
Rem create a table cache + pivot table
ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=rngData).CreatePivotTable _
TableDestination:=wspvtTbl.Range("A1"), TableName:="PivotTable1", Defaultversion:=xlpivoTableVersion12
End Sub |
Partager