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.