Pivot Table Show Details of Section in one Worksheet

[swapnilk]

Hi all,

Please refer to thread: pivot tables and showdetail/drill down

I am trying to to get a drill down of multiple cells from a pivot table into the same sheet. The solution posted by charlie1105 works and shows the data of multiple rows in one sheet. However, after a few seconds the excel workbook crashes.

Here is the code:

Sub DrillDownSpec()
Dim LRSource As Long, LRDest As Long
For Each cell1 In Selection
    cell1.ShowDetail = True
    If cellcounter = 0 Then
        ActiveSheet.Name = "PivotDrillDown"
    Else
        LRSource = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LRDest = Sheets("PivotDrillDown").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        Range("A2:A" & LRSource).EntireRow.Copy Sheets("PivotDrillDown").Cells(LRDest, 1)
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        Sheets("PivotDrillDown").Select
    End If
cellcounter = cellcounter + 1
Next cell1
End Sub

Can someone please help with the workbook crashing after running the above code. I have tried in Excel 2021 and Excel 365. Also, is it possible to remove the headers in the PivotDrillDown sheet and make it a continuous table with a common header (doesn't matter if it losses pivot properties and is a normal table).

Thanks in advance!