Trying to base a pivot table on another pivot table - getting run time error 1004

Im changing the pivotcache of an existing pivot table by specify sql in the commandtext property - works well but as soon as i base another pivot table on that pivot table and run the sub i get "run-time error 1004" i could try create another pivot table but would like to conserve space.. and also refresh the second,3rd,fourth, fifth.. pivottable/pivotchart.
Who is Participating?
alainbrydenConnect With a Mentor Commented:
Check the value of myval and myval2, see if they are being properly retrieved. The only think I can see generating an error is the concatenation, because you're just setting a property. Is the string being built correctly? If you try to step to the next line of code is the error generated again? Add a watch on the PivotCache object and see what its status is.

Impossible to know what you've done wrong without showing the code that is generating an error and the exact line the error is shown to occur on when you hit debug.
Jimmy_incAuthor Commented:
Sub ICU()
Dim myval,myval2
myval = Range("A1").Value
myval2 = Range("A2").Value  
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 BETWEEN #" & myval & "# AND #" & myval2 & "#"
End With
End Sub

the exact line is the commandtext line

Jimmy_incAuthor Commented:
Will try that
All Courses

From novice to tech pro — start learning today.