Results 1 to 4 of 4

Handling Pivot Tables with macros - code runs slow

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Handling Pivot Tables with macros - code runs slow

    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
    Last edited by Pichingualas; 02-12-2012 at 10:59 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1