views:

186

answers:

0

Hi,

Using Code to change the pivot cache of the pivottable specifically:

Dim pc As PivotCache, pt As PivotTable
Set pt = Sheets("Sheet1").PivotTables("PivotTable1")
Set pc = pt.PivotCache
With pc
.CommandType = xlCmdSql
.CommandText = "Select * From Query1 Where Query1.Date BETWEEN #" & startval & "#   AND    #" & endval & "#"
 .Refresh
End With
End Sub

I get: Run-time error '1004' Application-defined or object-defined error as soon as .CommandText is run.

And this code works perfectly with just one pivottable.

I must be able to have multiple pivottables on one worksheet(s) to be using the same pivotcache.