pattersontod
New Member
- Joined
- Jan 17, 2017
- Messages
- 2
I have a report that pulls in data from a database and updates existing pivot charts. The report has been running for a couple of years with not issues but recently started giving me the error "Run Time error 1004; Unable to set the visible property of the pivot class item" I am using 2007 Excel. Below is my code. The error stops on the line
"ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTH NUMBER").PivotItems(i).Visible = False"
Below is the code that updates one pivot chart. I don't understand why it quit working when it has worked for a couple of years without incident.
'This sectionrefreshes the Top Reasons per Press chart.
Sheets("Sheet12").Visible = True
<o> </o>Sheets("Sheet12").Select
<o>
</o>'Determine the parameters.
ValTotal3 = Sheets("ReferenceData").Range("A24").Value
ValEnd3 = ValTotal3 - 3
<o> </o>
ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").ClearValueFilters
'Set all pivot itemsbefore the selected parameters to Visible=False.
WithSheets("Sheet12").PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER")
For i =1 To ValTotal3
ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").PivotItems(i).Visible = True
Next i
For i = 1To ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").PivotItems.Count - Month(Date)
ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").PivotItems(i).Visible = False
Next i
ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").PivotItems("(blank)").Visible = False
<o> </o>
End With
<o> </o>
Sheets("47K Charts").Select
<o> </o>
Sheets("Sheet12").Visible = False
"ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTH NUMBER").PivotItems(i).Visible = False"
Below is the code that updates one pivot chart. I don't understand why it quit working when it has worked for a couple of years without incident.
'This sectionrefreshes the Top Reasons per Press chart.
Sheets("Sheet12").Visible = True
<o> </o>Sheets("Sheet12").Select
<o>
</o>'Determine the parameters.
ValTotal3 = Sheets("ReferenceData").Range("A24").Value
ValEnd3 = ValTotal3 - 3
<o> </o>
ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").ClearValueFilters
'Set all pivot itemsbefore the selected parameters to Visible=False.
WithSheets("Sheet12").PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER")
For i =1 To ValTotal3
ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").PivotItems(i).Visible = True
Next i
For i = 1To ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").PivotItems.Count - Month(Date)
ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").PivotItems(i).Visible = False
Next i
ActiveSheet.PivotTables("Top_Reasons_Per_Press").PivotFields("MONTHNUMBER").PivotItems("(blank)").Visible = False
<o> </o>
End With
<o> </o>
Sheets("47K Charts").Select
<o> </o>
Sheets("Sheet12").Visible = False