Pivot table question

[counterscc]

In a pivot table I am able to double click within the Pivot Table Values area and in doing so it opens a seperate tab within that same workbook with just the info I double clicked on. Is there a way so that when I double click in the Pivot Table Values area it opens in a whole new workbook as oppsed to just another tab in that same workbook? Any help would be greatly appreciated. Thanks

 

[markmzz]

Counterscc,

Important: make a copy of your workbook first.

Try this:

1. Copy the code below.

Private Sub Worksheet_BeforeDoubleClick( _
        ByVal Target As Range, Cancel As Boolean)
    Dim myName As String
 
    myName = ActiveSheet.Name
    On Error Resume Next
    Selection.ShowDetail = True
 
    If myName <> ActiveSheet.Name Then
        ActiveSheet.Move
    End If
End Sub

2. Click with the right mouse button on the tab of the worksheet containing the pivot table and choose View Code on the menu that appears.

3. A VBA window appears, paste the code you copied in step 1 in the VBA window.

4. Test on the sheet with the PivotTable.

Markmzz

 

[counterscc]

That worked out great!! Thank you very much!

 

[markmzz]

That worked out great!! Thank you very much!

Counterscc,

Thanks for the feedback.

Markmzz

 

[markmzz]

Counterscc,

Only a patch (in red in the code below).

Private Sub Worksheet_BeforeDoubleClick( _
        ByVal Target As Range, Cancel As Boolean)
    Dim myName As String
 
    myName = ActiveSheet.Name
    On Error Resume Next
    Selection.ShowDetail = True
 
   [COLOR=red][B]On Error GoTo 0[/B][/COLOR]
    
    If myName <> ActiveSheet.Name Then
        ActiveSheet.Move
    End If
End Sub

Markmzz