Pivot Table "Drill-Down" in new window

[Ada01]

Hello Excel Geniuses

When drilling down through a PivotTable line it creates a summary table of the line in a new worksheet in the same workbook...i want to do something a little different b/c I do not want a shared file to have an opportunity to me "messed up". I have scrapped my "BeforeDoubleClick.... attempts and everything else that has failed and i am left with my code that moves the first sheet is the workbook (although i am trying to figure if i can find the most recent sheet created or to force the new sheet to go to position one each time) to a new window so it can be saved without and changes to the master Workbook so end users can simply mess around with their new data.


1. I know that a double-click in any data area on the PivotTable creates a new sheet (in my case it always goes to the first position although I would like to search for the newest created sheet)

2. What i was trying to do was "listen" for that event and then run a simple move to new window that has the sample of data they would be looking for so it can be easily saved as new workbook and analyzed as they see fit.

Thanks

 

[DonkeyOte]

Ada01, if you opt to cross post the same question on multiple boards please have the courtesy to your fellow members to detail all relevant links:

http://www.excelforum.com/excel-programming/758197-vba-open-drill-down-in-new-workbook.html

 

[Ada01]

That would be my bad...

however, i did stumble across a solution but it needs some work in order to be efficient for an workbook set up.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    Application.ScreenUpdating = False
    
    Sheets(1).Select
    Sheets(1).Move
    
End Sub

the part of the code that I need to tweak is the part that ensures the NewSheet is the one that is selected b/c this code is going to go across several workbooks that are not all laid out the same so the first sheet will not always be the one that is created.

Thanks

 

[DonkeyOte]

As I see it the above approach is flawed unless it's impossible for anyone to add a sheet other than via pivot drill through.
(note that when a sheet is newly added it is automatically the active sheet)

The approach detailed in the earlier link [replicated below] should work - stored in ThisWorkbook within VBE:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim PT As PivotTable
    If Sh.PivotTables.Count > 0 Then
        For Each PT In Sh.PivotTables
            If Not Intersect(Target, PT.DataBodyRange) Is Nothing Then
                Target.ShowDetail = True
                ActiveSheet.Move
                Exit Sub
            End If
        Next PT
    End If
End Sub