• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

populating multiple pivot charts on multiple sheets

drosenbe08132

New Member
I'm trying to loop through sheets, each sheet is associated with a different CSV file via a query.I can create the query and then create the pivot table and graph on each sheet.
When I try to populate the pivots, I am getting a "subscript out of range" error. I manually record the actions and see that the field appears to have a subscript, which I am assuming the index of the query.

Code:
ActiveSheet.PivotTables("pivot_AEC2_5a").AddDataField ActiveSheet.PivotTables( _
"pivot_AEC2_5a").CubeFields("[Measures].[Count of answer_key 2]"), _
"Count of answer_key"
I have create variables for both the subscript and the whole phrase, and it appears to work in isolation, but then when I put it into the same loop that creates the query and pivot tables, I keep getting the error.
BTW, if I don't use a subscript in the code, then that field is always pulled from the first query results. The other fields that I want are correctly pulled from the correct query (without any subscript)
Here is the entire snippet of what is recorded.

Code:
ActiveChart.ChartArea.Select
With ActiveChart.PivotLayout.PivotTable.CubeFields( _
               "[CS2_AEC2_5a].[question_text]")
                .Orientation = xlColumnField
                .Position = 1
End With
ActiveSheet.PivotTables("pivot_AEC2_5a").CubeFields.GetMeasure _
           "[CS2_AEC2_5a].[answer_key]", xlCount, "Count of answer_key 2"
            ActiveSheet.PivotTables("pivot_AEC2_5a").AddDataField ActiveSheet.PivotTables( _
            "pivot_AEC2_5a").CubeFields("[Measures].[Count of answer_key 2]"), _
            "Count of answer_key"
With ActiveChart.PivotLayout.PivotTable.CubeFields( _
                       "[CS2_AEC2_5a].[answer_text]")
               .Orientation = xlRowField
                .Position = 1
End With
Any thoughts?
a) use code - tags
b) reread Forum Rules, especially How to get the Best Results at Chandoo.org
 
Last edited by a moderator:

drosenbe08132

New Member
Thanks for the reply, unfortunately the 2 references have nothing to do with what I'm having an issue with. Please, instead of telling me that I did not provide adequate information, please tell me what else you need?
 

herofox

Active Member
drosenbe08132
As a new member, You should reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in... chandoo.org
 
Top