MarkCBB
Active Member
- Joined
- Apr 12, 2010
- Messages
- 497
HI there,
I am using this code to filter every PivotTable on a worksheet by an item that I select from a ComboBox, but the code takes some time to run, and there are only 15 pivot Tables and there are 30 Items.
I am using this code to filter every PivotTable on a worksheet by an item that I select from a ComboBox, but the code takes some time to run, and there are only 15 pivot Tables and there are 30 Items.
Code:
Private Sub ComboBox1_Click()
Dim MyReport As Worksheet
Dim i As Integer
Dim PTCount As Integer
Dim MyWord As String
i = 0
Set MyReport = Sheets("Report_QSR")
MyReport.Range("A2").Value = ComboBox1.Value
PTCount = MyReport.PivotTables.Count
MyWord = MyReport.Range("A2").Value
With Application
.EnableAnimations = False
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
End With
Do
i = i + 1
With MyReport.PivotTables(i).PivotFields("CHANNEL")
On Error Resume Next
.PivotItems(MyWord).Visible = True
On Error GoTo 0
For Each Pi In .PivotItems
On Error Resume Next
If Pi.Name <> MyWord Then Pi.Visible = False
On Error GoTo 0
Next Pi
End With
Application.StatusBar = i & " Number of reports updated"
Loop Until i = PTCount
With Application
.StatusBar = ""
.EnableAnimations = True
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Private Sub ComboBox1_DropButt*******()
Dim i As Integer
ComboBox1.Clear
i = 6
Do
i = i + 1
ComboBox1.AddItem Sheets("REPORT_QSR").Range("H" & i).Value
Loop Until Range("H" & i).Offset(1, 0).Value = ""
End Sub