VBA code. Pivot table drilldown on a new windows form

[hcova]

Hi There.
If you double click on a pivot table cell, a new sheet automatically opens and displays the database list. Then you need to delete it manually and go back to your pivot table.
What I really would like to do is create an Excel VBA Code so when you double click in the cell, the database list will be displayed in a new floating window instead of the default new sheet.
This new floating window should keep open until the user decides to close it.
The user needs to keep open many floating windows at the same time, according to the number of cells the user has double-clicked on the pivot table.
Let me show this with images:
1) This is the current pivot table

2) Then the user makes a double click in B15 Cell and a pop-up appears displaying the database.
Next, the user makes a double click in another cell (C17) and a new pop-up appears in the database instead of a new sheet.
A design detail: The double-clicked cells change their background colors as well as the pop-up window borders.

Any code, help, or link will be welcomed
Best Regards
Hernán

 

[hcova]

Now I saw some lines that are not necessary.

'to the ThisWorkbook module
Dim vRed As Integer, vGreen As Integer, vBlue As Integer

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
    Select Case Sh.Name
        Case "PT", "PT2", "PT3"
            On Error Resume Next
            For vN = 0 To UBound(vA)
                Sh.Range(vA(vN)).Interior.Color = xlNone
            Next vN
            vPTName = Sh.Name
            If ActiveCell = "" Then
                ActiveCell.Interior.Color = xlNone
                For i = 0 To UBound(vA)
                    Range(vA(i)).Interior.Color = xlNone
                Next i
            Else
                vA = Split(Selection.Address, ",")
                For vN = 0 To UBound(vA)
                    vRed = Int(255 * Rnd)
                    vGreen = Int(255 * Rnd)
                    vBlue = Int(255 * Rnd)
                    Sh.Range(vA(vN)).Interior.Color = _
                        RGB(vRed, vGreen, vBlue)
                Next vN
            End If
    End Select
   
End Sub

'to the Standard module
Dim vTableName As String
Dim vN As Integer, vNR2 As Long, vNR As Long, vNC As Integer
Public vA, vPTName As String
Public vNewExcel As Excel.Application

Sub Macro1()
   
    vNR2 = 1
    Application.OnTime Now + TimeValue("00:00:01"), "ActivateNewView"

End Sub

Sub ActivateNewView()
   
    Application.ScreenUpdating = False
    Set vNewExcel = New Excel.Application
    With vNewExcel
        .Workbooks.Add
        .Caption = "NEW VIEW"
        .ActiveWindow.Caption = vPTName
        .ActiveWindow.ActiveSheet.Name = vPTName
    End With
    For vN = 0 To UBound(vA)
        Range(vA(0)).Select
        Application.DoubleClick
        vNR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
        vNC = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
        vTableName = "NewViewTable" & ActiveSheet.Name
        With vNewExcel.ActiveWorkbook.ActiveSheet
            .ListObjects.Add(xlSrcRange, .Range(.Cells(vNR2, 1), _
            .Cells(vNR2 + vNR - 1, vNC)), , xlYes).Name = vTableName
        End With
        ActiveSheet.Range("A1", Cells(vNR, vNC)).Copy
        vNewExcel.ActiveWorkbook.ActiveSheet. _
            Range("A" & vNR2).PasteSpecial xlPasteValues
        vNR2 = vNR2 + vNR
        Application.DisplayAlerts = False
        ActiveSheet.Delete
    Next vN
    For vN = 0 To UBound(vA)
        ActiveSheet.Range(vA(vN)).Interior.Color = xlNone
    Next vN
    vNewExcel.Visible = True

End Sub

Great EXCEL MAX.
Thanks a lot for your time and patience!!
Warm regards
Hernán

 

[EXCEL MAX]

Cheers!?

 

[Mark Milan]

Cheers!?

Cheers!?

Hi Max

You are great!
How should it be modified if you might double click on many pivot table cells (not a unique pivot table) in the same sheet?

Thanks you
Mark

 

[Mark Milan]

...I got this error when running macro...
What I'd like is :
1) I double click a pivot table area (drill down function)
2) I get returned data in a new workbook

Otherwise, I'd like to add a button in the sheet where pivot tables are included and If i select a cell within pivot table area, I click on the button and returned data will appear in a new workbook...... Is that possible? how?

thanks in advance
Mark