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

Posted on 2010-01-07
Last Modified: 2012-05-08
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.
Question by:Jimmy_inc
    LVL 20

    Expert Comment

    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.

    Author Comment

    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

    LVL 20

    Accepted Solution

    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.


    Author Comment

    Will try that

    Author Comment


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now