none
VBA create Pivot table by double click on existing Pivot table field RRS feed

  • Question

  • Hi,

    Is there a way to create another pivot table based on double click on existing pivot table cell?

    Thanks

    Wilson

    Friday, November 21, 2014 5:43 AM

Answers

  • Hi Wilson,

    By default, the Pivot Table in Excel worksheet has some built-in double-click actions, for example, double click the rows, it'll prompt you to drill down the detailed information, double click the values, it'll create another worksheet to show the detailed data. If you want to add some custom actions, one workable solution is to utilize the worksheet's BeforeDoubleClick event, determine if the user clicks the range in the Pivot Table, cancel the default action, then create another Pivot Table:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim rng As Range
    
        '~~> This is your pivot table range. Change as applicable
        Set rng = Range("A3:B7")
    
        'Check if the double click happend in the Pivot
        If Not Intersect(Target, rng) Is Nothing Then
            'Cancel Double click
            Cancel = True
            CreateAnotherPivotTable
        End If
    End Sub
    
    Private Sub CreateAnotherPivotTable()
        'Create the Pivot Table here
    End Sub

    For how to create Pivot Table with VBA code, please check this MSDN document:

    PivotCache.CreatePivotTable Method (Excel)


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Wilson Wu Friday, November 28, 2014 6:34 AM
    Monday, November 24, 2014 6:40 AM
    Moderator

All replies

  • Hi Wilson,

    By default, the Pivot Table in Excel worksheet has some built-in double-click actions, for example, double click the rows, it'll prompt you to drill down the detailed information, double click the values, it'll create another worksheet to show the detailed data. If you want to add some custom actions, one workable solution is to utilize the worksheet's BeforeDoubleClick event, determine if the user clicks the range in the Pivot Table, cancel the default action, then create another Pivot Table:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim rng As Range
    
        '~~> This is your pivot table range. Change as applicable
        Set rng = Range("A3:B7")
    
        'Check if the double click happend in the Pivot
        If Not Intersect(Target, rng) Is Nothing Then
            'Cancel Double click
            Cancel = True
            CreateAnotherPivotTable
        End If
    End Sub
    
    Private Sub CreateAnotherPivotTable()
        'Create the Pivot Table here
    End Sub

    For how to create Pivot Table with VBA code, please check this MSDN document:

    PivotCache.CreatePivotTable Method (Excel)


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Wilson Wu Friday, November 28, 2014 6:34 AM
    Monday, November 24, 2014 6:40 AM
    Moderator
  • Hi Caillen,

    Thanks. 

    Wilson

    Friday, November 28, 2014 6:34 AM