M
Mathieu
Hi,
I have several pivot table on one page. The only way I found so far to
update them correctely (data and filter) is the code below. I repeat it for
each table on the sheet. Is there a better way to do it? BTW " Tableau croisé
dynamique1" is the french of "Pivot table". The reasons why I do the plant
by false is that sometimes some plant does not appears in the data, resulting
in error when I do the "true" approach
With ActiveSheet.PivotTables("Tableau croisé dynamique1")
.PivotCache.Refresh
End With
With ActiveSheet.PivotTables("Tableau croisé
dynamique1").PivotFields("Plant")
.PivotItems("2110").Visible = False
.PivotItems("3100").Visible = False
.PivotItems("3103").Visible = False
.PivotItems("3104").Visible = False
.PivotItems("3106").Visible = False
.PivotItems("3130").Visible = False
.PivotItems("4107").Visible = False
.PivotItems("4108").Visible = False
.PivotItems("4109").Visible = False
.PivotItems("4121").Visible = False
.PivotItems("4123").Visible = False
.PivotItems("4124").Visible = False
.PivotItems("4125").Visible = False
.PivotItems("4126").Visible = False
.PivotItems("5120").Visible = False
.PivotItems("5121").Visible = False
.PivotItems("5122").Visible = False
.PivotItems("5125").Visible = False
.PivotItems("5126").Visible = False
.PivotItems("5127").Visible = False
.PivotItems("5130").Visible = False
.PivotItems("5131").Visible = False
.PivotItems("5132").Visible = False
.PivotItems("(vide)").Visible = False
End With
End Sub
then following and so on
With ActiveSheet.PivotTables("Tableau croisé dynamique2")
.PivotCache.Refresh
End With
With ActiveSheet.PivotTables("Tableau croisé
dynamique2").PivotFields("Plant")
.PivotItems("2110").Visible = False
.PivotItems("3100").Visible = False
.PivotItems("3103").Visible = False
.PivotItems("3104").Visible = False
.PivotItems("3106").Visible = False
.PivotItems("3130").Visible = False
.PivotItems("4107").Visible = False
.PivotItems("4108").Visible = False
.PivotItems("4109").Visible = False
.PivotItems("4121").Visible = False
.PivotItems("4123").Visible = False
.PivotItems("4124").Visible = False
.PivotItems("4125").Visible = False
.PivotItems("4126").Visible = False
.PivotItems("5120").Visible = False
.PivotItems("5121").Visible = False
.PivotItems("5122").Visible = False
.PivotItems("5125").Visible = False
.PivotItems("5126").Visible = False
.PivotItems("5127").Visible = False
.PivotItems("5130").Visible = False
.PivotItems("5131").Visible = False
.PivotItems("5132").Visible = False
.PivotItems("(vide)").Visible = False
End With
End Sub
I have several pivot table on one page. The only way I found so far to
update them correctely (data and filter) is the code below. I repeat it for
each table on the sheet. Is there a better way to do it? BTW " Tableau croisé
dynamique1" is the french of "Pivot table". The reasons why I do the plant
by false is that sometimes some plant does not appears in the data, resulting
in error when I do the "true" approach
With ActiveSheet.PivotTables("Tableau croisé dynamique1")
.PivotCache.Refresh
End With
With ActiveSheet.PivotTables("Tableau croisé
dynamique1").PivotFields("Plant")
.PivotItems("2110").Visible = False
.PivotItems("3100").Visible = False
.PivotItems("3103").Visible = False
.PivotItems("3104").Visible = False
.PivotItems("3106").Visible = False
.PivotItems("3130").Visible = False
.PivotItems("4107").Visible = False
.PivotItems("4108").Visible = False
.PivotItems("4109").Visible = False
.PivotItems("4121").Visible = False
.PivotItems("4123").Visible = False
.PivotItems("4124").Visible = False
.PivotItems("4125").Visible = False
.PivotItems("4126").Visible = False
.PivotItems("5120").Visible = False
.PivotItems("5121").Visible = False
.PivotItems("5122").Visible = False
.PivotItems("5125").Visible = False
.PivotItems("5126").Visible = False
.PivotItems("5127").Visible = False
.PivotItems("5130").Visible = False
.PivotItems("5131").Visible = False
.PivotItems("5132").Visible = False
.PivotItems("(vide)").Visible = False
End With
End Sub
then following and so on
With ActiveSheet.PivotTables("Tableau croisé dynamique2")
.PivotCache.Refresh
End With
With ActiveSheet.PivotTables("Tableau croisé
dynamique2").PivotFields("Plant")
.PivotItems("2110").Visible = False
.PivotItems("3100").Visible = False
.PivotItems("3103").Visible = False
.PivotItems("3104").Visible = False
.PivotItems("3106").Visible = False
.PivotItems("3130").Visible = False
.PivotItems("4107").Visible = False
.PivotItems("4108").Visible = False
.PivotItems("4109").Visible = False
.PivotItems("4121").Visible = False
.PivotItems("4123").Visible = False
.PivotItems("4124").Visible = False
.PivotItems("4125").Visible = False
.PivotItems("4126").Visible = False
.PivotItems("5120").Visible = False
.PivotItems("5121").Visible = False
.PivotItems("5122").Visible = False
.PivotItems("5125").Visible = False
.PivotItems("5126").Visible = False
.PivotItems("5127").Visible = False
.PivotItems("5130").Visible = False
.PivotItems("5131").Visible = False
.PivotItems("5132").Visible = False
.PivotItems("(vide)").Visible = False
End With
End Sub