Pivot table

N

nc

I have written the follwing codes to update the hidden
items of two pivot tables. Any help would be appreciated
in summarizing the code and reducing the run time. How
can I then attach it to different combo on different
sheet with much repetitive coding? Thanks.


Private Sub ComboBox1_Change()

Application.ScreenUpdating = False

With Sheets("Newton share Report").PivotTables
("PivotTable1").PivotFields("Date")

On Error Resume Next

.PivotItems("Sep").Visible = False
.PivotItems("Oct").Visible = False

End With

With Sheets("EFG share report").PivotTables
("PivotTable2").PivotFields("Date")


.PivotItems("Sep").Visible = False
.PivotItems("Oct").Visible = False

End With

Select Case ComboBox1.ListIndex

Case 0:

Case 1: With Sheets("Newton share Report").PivotTables
("PivotTable1").PivotFields("Date")
.PivotItems("Sep").Visible = True
End With

With Sheets("EFG share report").PivotTables
("PivotTable2").PivotFields("Date")
.PivotItems("Sep").Visible = True
End With

Case 2: With Sheets("Newton share Report").PivotTables
("PivotTable1").PivotFields("Date")
.PivotItems("Sep").Visible = True
.PivotItems("Oct").Visible = True
End With

With Sheets("EFG share report").PivotTables
("PivotTable2").PivotFields("Date")
.PivotItems("Sep").Visible = True
.PivotItems("Oct").Visible = True
End With


With Sheets("Newton share Report").Columns("A:A")
.EntireColumn.AutoFit
.HorizontalAlignment = xlLeft
End With

With Sheets("EFG share Report").Columns("A:A")
.EntireColumn.AutoFit
.HorizontalAlignment = xlLeft
End With


Sheets("EFG share Report").ComboBox1.Text = Sheets
("Newton share Report").ComboBox1.Text

Application.ScreenUpdating = True

End Sub
 
Top