I need help making my code run faster. My code is supposed to, upon double clicking on 1 item from a list of items that are present on a pivot table, hide all of the items but the one the user selected. Also if the user wants to show all the items again, he just has to double click on 1 of the first two cells of the list. It does what I want, but it takes a bit long to do it. Here it goes:
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, ByRef Cancel As Boolean)
'Filters pivot tables associated to the item you click on
Dim x As Byte 'For knowing what stage of the filter is running
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strPF As String 'Name of the pivot field to be filtered
Dim strPI As String 'Only item to stay visible
Application.ScreenUpdating = False
If Sh.Name = "Clientes" Then
strPF = "Razón social"
ElseIf Sh.Name = "RRCC" Then
strPF = "Representante comercial"
ElseIf Sh.Name = "Productos" Then
strPF = "Ejercicio/Período"
Else
Exit Sub
End If
If Target.Column = 1 And Target.Row <= Range("A1").CurrentRegion.Rows.Count Then
If Target.Row <= 2 Then
x = 1
If x = 1 Then
Sheets("TD MUsMP").Select
End If
DontFilterPT:
Set pt = ActiveSheet.PivotTables(1)
Application.DisplayAlerts = False
On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
.AutoSort xlAscending, .SourceName
End With
Application.DisplayAlerts = True
x = x + 1
If x = 2 Then
Sheets("TD CMN").Select
GoTo DontFilterPT
End If
On Error GoTo 0
Else
x = 1
strPI = Target.Value
If x = 1 Then
Sheets("TD MUsMP").Select
End If
filterPT:
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields(strPF)
Application.DisplayAlerts = False
On Error Resume Next
With pf
.AutoSort xlManual, .SourceName
For Each pi In pf.PivotItems
pi.Visible = False
If pi.Value = strPI Then
pi.Visible = True
End If
Next pi
.AutoSort xlAscending, .SourceName
End With
Application.DisplayAlerts = True
x = x + 1
If x = 2 Then
Sheets("TD CMN").Select
GoTo filterPT
End If
On Error GoTo 0
End If
Cancel = True
End If
Sheets(Sh.Name).Select
Application.ScreenUpdating = True
End Sub
Thanks in advance for all your effort
Bookmarks