If your worksheets are established exactly as you say (exactly means exactly, such as the "DrillDown" sheet tab being spelled that way you said, and not "Drill Down" with a space), then place this in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedures into the large white area that is the workbook module:
Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
If CS <> "" Then
With Application
.ScreenUpdating = False
Dim NR&
With Sheets("DrillDown")
If WorksheetFunction.CountA(.Rows(1)) = 0 Then
NR = 1
Else
NR = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2
End If
Range("A1").CurrentRegion.Copy .Cells(NR, 1)
End With
.DisplayAlerts = False
ActiveSheet.Delete
.DisplayAlerts = True
Sheets(CS).Select
.ScreenUpdating = True
End With
End If
End Sub
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If ActiveSheet.Name = "Pivot" Then
CS = "Pivot"
ElseIf ActiveSheet.Name = "DrillDown" Then
If Not IsEmpty(Target) Then
If Target.Row > Range("A1").CurrentRegion.Rows.Count + 1 _
Or Target.CurrentRegion.Cells(1, 1).Address = "$A$1" Then
Cancel = True
With Target.CurrentRegion
.Resize(.Rows.Count + 1).EntireRow.Delete
End With
End If
End If
End If
End Sub
Then, while in the VBE, click on Insert > Module and paste this into that new module:
Press Alt+Q to return to the worksheet.
Now, on your "Pivot" sheet, double click in the Data section, and the drill-down records will be placed on the next available row in the DrillDown sheet. It will be best if you clear the DrillDown sheet of all drilled data sets and start fresh.
Four notes:
(1)
This only applies to double-clicking in the data section of the pivot table, because as opposed to the pivot table's row and column header fields, the data section is what generates the insertion of a new sheet when double-clicked. Now, no new sheet will be added, which is the purpose of your request.
(2)
You can double-click the data section to your heart's content and the drill-down records will stack themselves in the DrillDown sheet.
(3)
As an extra feature for you, I included code such that if you want to quickly delete a drill down record, go to the DrillDown sheet and double click in any cell in any record, and that record will be deleted, shifting all drill-down recordsets up.
(4)
The code renders null the ability to insert a new sheet.
