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.
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.
Any thoughts?
a) use code - tags
b) reread Forum Rules, especially How to get the Best Results at Chandoo.org
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
a) use code - tags
b) reread Forum Rules, especially How to get the Best Results at Chandoo.org
Last edited by a moderator: