Cleaning out pivot table and then using same pivot vache

[Lionheart]

I have created a pivot cache and pivot table using external data from a stored proc. What I would like to do is clear all data from the pivot table but using the same pivot cache reinsert the fields.

The following is the code for clearing the pivot table

[VBA] For index = shtMain.PivotTables.Count To 1 Step -1
If Application.Version = "12.0" Then shtMain.PivotTables(index).ClearTable
Next index
[/VBA]

For initally creating the cache I have used

[VBA] Dim oPivotCache As PivotCache
Set oPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)

With oPivotCache
.Connection = SQL_CONNECTION_STRING
.CommandType = xlCmdSql
.CommandText = GetPivotDataCmd()
Set oPivotTable = .CreatePivotTable( _
TableDestination:=shtMain.Cells(10, 3), _
TableName:=PIVOTTABLE_1, _
DefaultVersion:=xlPivotTableVersion10)
End With
[/VBA]

What I need is to have a command something like

[VBA]Dim oPivotCache As PivotCache
Set oPivotCache = ActiveWorkbook.PivotCaches WHAT GOES HERE?[/VBA]

So that it uses the same data that is stored in the original cache?

Thx

LH

[Sean.DiSanti]

so you want to use the same data, and the same fields? maybe you just RefreshTable ?

[Lionheart]

Thanks. I had the code I needed in the end, just needed to add a sheet reference for the cache. Thought that as it was the only sheet it would be okay, but not the case.

LH