PDA

View Full Version : Help - Automate Pivot Filter Changes



rachelmt91
07-08-2013, 03:52 AM
Hi

I am looking for some help with some code I have written - I've only ever done a bit of VBA before so this is more guess work for me.

Basically... I have a workbook that has a master sheet full of data. The data comes under a 'week ending' column. There is tons of data and from this data we conduct trending with the use of pivot tables/charts. There are another 4 sheets included in the workbook with 4 pivot tables on each sheet. Each pivot table has a drop down where you can change the filter to show the different data on a 'week ending' basis. Every week we report from these graphs and show the latest 4 weeks worth of data on each chart. This means manually having to refresh each table to show the new data from the new week and then remove the oldest date from the filter and tick the latest data to show it.

The aim of my code is to be able to automate this process. The code I have written is semi-succesful... I have a macro assigned to a button to refresh the pivots on just these 4 sheets (there are more sheets in the workbook) and then i have another macro assigned to a button that then pops up with 2 input boxes. The first asks for the date wishing to be REMOVED from the filter and the second asks for the data wishing to be ADDED to the filter.

When i wrote the code i took a copy of the master workbook and coded away... after a while it worked! Perfect, it did what i wanted. So then i thought, hang on i better add new data into the master sheet and test it. What happened then was this... the new data showed up on the filter fine... when i entered the new data to be added to the filter using my macro, it doesnt work. I get the error of ' Unable to get the PivotItems property of the Pivot class'. Note: this only happens when i try using dates that are NEW since i wrote the code. All the old dates in the filter still work and can be added and removed. :banghead:

I have pasted the code below and any help/suggestion would be greatly appreciated. I have also looked into the whole pivot cache thing but this doesnt do anything to fix the issue either... (Excel 2010)


Thank you for reading! :hi:


Sub PivotUpdate()
Dim RemoveDate As String
RemoveDate = InputBox("Enter date to be REMOVED from the filter")
Dim AddDate As String
AddDate = InputBox("Enter date to be ADDED to the filter")

With Sheet5.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet5.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet5.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet5.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet5.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet5.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet5.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet5.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet6.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet6.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet6.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet6.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet6.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet6.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet6.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet6.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet7.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet7.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet7.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet7.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet7.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet7.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet7.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet7.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet8.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet8.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet8.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet8.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

With Sheet8.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet8.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With
With Sheet8.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
End With

With Sheet8.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(AddDate).Visible = True
End With

End Sub

Aussiebear
07-08-2013, 04:09 PM
This shortens up your current code somewhat, but doesn't necessarily answer your question.
Sub PivotUpdate()
Dim RemoveDate As String
RemoveDate = InputBox("Enter date to be REMOVED from the filter")
Dim AddDate As String
AddDate = InputBox("Enter date to be ADDED to the filter")

With Sheet5.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet5.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet5.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet5.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet6.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet6.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet6.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet6.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet7.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet7.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet7.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet7.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet8.PivotTables("PivotTable1").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet8.PivotTables("PivotTable5").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet8.PivotTables("PivotTable3").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

With Sheet8.PivotTables("PivotTable2").PivotFields("Week Ending")
.PivotItems(RemoveDate).Visible = False
.PivotItems(AddDate).Visible = True
End With

End Sub

Simon Lloyd
07-08-2013, 11:04 PM
You should get your data from Dynamic Named Range (http://www.contextures.com/xlNames01.html) this way when you add or remove data it will be available to the pivot table, right now your range is static which is why the new data doesn't do what you want.