Trouble Creating Two Seperate Pivot Caches

[mattc7786]

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]

 

[mattc7786]

I have removed this part of the code

Else
Set pc = ThisWorkbook.PivotCaches(1)

[/code]

I thought maybe this was my problem but now I am getting a different error and the pivot table isn't even being created. I am getting a Run-time error '424': Object required.

It is then highlight the portion of the code that I removed the above piece from.

 

[mattc7786]

I have figured it out. I was a bit too fancy with my code and started from scratch and got it to work.