Trouble Creating Two Seperate Pivot Caches

mattc7786

New Member
Joined
Oct 1, 2018
Messages
4
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]
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top