M
MikeZz
Hi Experts,
I have a pivot table with some fields containing a large amount of items.
I want to have a button that first makes ALL items visible in a particular
field and then just hides certain ones. This way as more values are added,
the macro doesn't have to worry about clearing items unknown to me now.
The Record Macro works great to specify exactly what I want to make visible
or not but it I was wondering if there's a global property for a field to
make all items visible? Or do I have to go through each item, see if it's
visible and if it's not, set visible = true?
Also, I'm not sure if there's a way, but maybe if someone can show me other
ways to speed up pivot changes such as turning off refreshing & calculation
etc and then turning it back on may be of help.
Thanks for the help,
Mike Zz
Below is the subroutine I have from recording a macro.
All is original except where I tried to use If items.visible = false then
make it true.
That made a little improvement but it still seems to be a little slow on the
refresh.
Sub Set_Pivot()
'
ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier").CurrentPage = _
"XXX"
' Range("A5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("OEM Plant")
.PivotItems("Plant 1").Visible = False
End With
' Range("A7").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
If .PivotItems("ISU").Visible = True Then .PivotItems("ISU").Visible
= False
If .PivotItems("SUZ").Visible = True Then .PivotItems("SUZ").Visible
= False
If .PivotItems("WUL").Visible = True Then .PivotItems("WUL").Visible
= False
End With
' Range("A8").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Model")
If .PivotItems("SGM12").Visible = True Then
..PivotItems("SGM12").Visible = False
If .PivotItems("SGM18").Visible = True Then
..PivotItems("SGM18").Visible = False
If .PivotItems("SGM200").Visible = True Then
..PivotItems("SGM200").Visible = False
If .PivotItems("SGM201").Visible = True Then
..PivotItems("SGM201").Visible = False
If .PivotItems("SGM258").Visible = True Then
..PivotItems("SGM258").Visible = False
If .PivotItems("SGM308").Visible = True Then
..PivotItems("SGM308").Visible = False
If .PivotItems("SGM618/J200").Visible = True Then
..PivotItems("SGM618/J200").Visible = False
If .PivotItems("SGM985").Visible = True Then
..PivotItems("SGM985").Visible = False
If .PivotItems("SGME10").Visible = True Then
..PivotItems("SGME10").Visible = False
If .PivotItems("SGME11").Visible = True Then
..PivotItems("SGME11").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("OEM").CurrentPage =
"GM"
' Range("B12").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PAC")
If .PivotItems("EL").Visible = True Then .PivotItems("EL").Visible =
False
End With
End Sub
I have a pivot table with some fields containing a large amount of items.
I want to have a button that first makes ALL items visible in a particular
field and then just hides certain ones. This way as more values are added,
the macro doesn't have to worry about clearing items unknown to me now.
The Record Macro works great to specify exactly what I want to make visible
or not but it I was wondering if there's a global property for a field to
make all items visible? Or do I have to go through each item, see if it's
visible and if it's not, set visible = true?
Also, I'm not sure if there's a way, but maybe if someone can show me other
ways to speed up pivot changes such as turning off refreshing & calculation
etc and then turning it back on may be of help.
Thanks for the help,
Mike Zz
Below is the subroutine I have from recording a macro.
All is original except where I tried to use If items.visible = false then
make it true.
That made a little improvement but it still seems to be a little slow on the
refresh.
Sub Set_Pivot()
'
ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier").CurrentPage = _
"XXX"
' Range("A5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("OEM Plant")
.PivotItems("Plant 1").Visible = False
End With
' Range("A7").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Brand")
If .PivotItems("ISU").Visible = True Then .PivotItems("ISU").Visible
= False
If .PivotItems("SUZ").Visible = True Then .PivotItems("SUZ").Visible
= False
If .PivotItems("WUL").Visible = True Then .PivotItems("WUL").Visible
= False
End With
' Range("A8").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Model")
If .PivotItems("SGM12").Visible = True Then
..PivotItems("SGM12").Visible = False
If .PivotItems("SGM18").Visible = True Then
..PivotItems("SGM18").Visible = False
If .PivotItems("SGM200").Visible = True Then
..PivotItems("SGM200").Visible = False
If .PivotItems("SGM201").Visible = True Then
..PivotItems("SGM201").Visible = False
If .PivotItems("SGM258").Visible = True Then
..PivotItems("SGM258").Visible = False
If .PivotItems("SGM308").Visible = True Then
..PivotItems("SGM308").Visible = False
If .PivotItems("SGM618/J200").Visible = True Then
..PivotItems("SGM618/J200").Visible = False
If .PivotItems("SGM985").Visible = True Then
..PivotItems("SGM985").Visible = False
If .PivotItems("SGME10").Visible = True Then
..PivotItems("SGME10").Visible = False
If .PivotItems("SGME11").Visible = True Then
..PivotItems("SGME11").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("OEM").CurrentPage =
"GM"
' Range("B12").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PAC")
If .PivotItems("EL").Visible = True Then .PivotItems("EL").Visible =
False
End With
End Sub