Hi team,
I am new to Excel Macros and VBA. I am working on a report summary and have managed to create a code that will create a pivot table and filter it. I have another sheet in the same workbook that I want to do the same thing with. When I use the following code and change the names appropriately it still is using the same pivot cache from the other sheet. I need it to make a new pivot cache. What is the best way to do that with what I have below?
Thanks,
Matt
Sub CreatingAndEditingPivotTable2()
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
If ThisWorkbook.PivotCaches.Count = 0 Then
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=wsKWH.Name & "!" & wsKWH.Range("A1").CurrentRegion.Address, _
Version:=xlPivotTableVersion15)
Else
Set pc = ThisWorkbook.PivotCaches(1)
End If
Worksheets.Add
Range("A3").Select
Set pt = pc.CreatePivotTable( _
TableDestination:=ActiveCell, _
TableName:="KWHPivot")
Set pf = pt.PivotFields("KWH Diff")
pf.Orientation = xlDataField
Set pf = pt.PivotFields("Curr. KWH")
pf.Orientation = xlDataField
Set pf = pt.PivotFields("Prv. KWH")
pf.Orientation = xlDataField
Set pf = pt.PivotFields("Cust ID")
pf.Orientation = xlRowField
Set pf = pt.PivotFields("Market")
pf.Orientation = xlPageField
ActiveSheet.Name = "KWH Summary 1000+"
End Sub
[/code]
I am new to Excel Macros and VBA. I am working on a report summary and have managed to create a code that will create a pivot table and filter it. I have another sheet in the same workbook that I want to do the same thing with. When I use the following code and change the names appropriately it still is using the same pivot cache from the other sheet. I need it to make a new pivot cache. What is the best way to do that with what I have below?
Thanks,
Matt
Sub CreatingAndEditingPivotTable2()
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf As PivotField
If ThisWorkbook.PivotCaches.Count = 0 Then
Set pc = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=wsKWH.Name & "!" & wsKWH.Range("A1").CurrentRegion.Address, _
Version:=xlPivotTableVersion15)
Else
Set pc = ThisWorkbook.PivotCaches(1)
End If
Worksheets.Add
Range("A3").Select
Set pt = pc.CreatePivotTable( _
TableDestination:=ActiveCell, _
TableName:="KWHPivot")
Set pf = pt.PivotFields("KWH Diff")
pf.Orientation = xlDataField
Set pf = pt.PivotFields("Curr. KWH")
pf.Orientation = xlDataField
Set pf = pt.PivotFields("Prv. KWH")
pf.Orientation = xlDataField
Set pf = pt.PivotFields("Cust ID")
pf.Orientation = xlRowField
Set pf = pt.PivotFields("Market")
pf.Orientation = xlPageField
ActiveSheet.Name = "KWH Summary 1000+"
End Sub
[/code]