asked on

recover pivot table data source. (Show all details)

my workbook has two sheets, Pivot and Data.
The pivot table's original soure was Data!a1:c100

Unfortunately, I accidentally deleted column c then closed the book.

I recovered the lost column by double clicking on each of the cells in the pivot table. Each double click create a worksheet with the original rows that contributed to that pivot cell.

I used VBA to automate this and merge all of the sheets into a single sheet.

Is there an easier way to have done this without VBA?

Sub PivotShowAllDetail()
'
' Macro2 Macro
'

'
Dim cell As Range, ary As Variant
Dim Sht1 As Worksheet
Dim orig As Range
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim nextRow As Long
Set orig = Selection
    For Each cell In Selection
        cell.ShowDetail = True
        If Sht1 Is Nothing Then
            Set Sht1 = ActiveSheet
            nextRow = Sht1.UsedRange.Rows.count + 1
        Else
            ary = ActiveSheet.UsedRange
            
            With Sht1.UsedRange.Cells(nextRow, 1)
                  .resize(UBound(ary, 1), UBound(ary, 2)) = ary
                  .EntireRow.Delete ' delete header row
                  nextRow = nextRow + ActiveSheet.UsedRange.Rows.count - 1
            End With
            Application.DisplayAlerts = False
            ActiveSheet.Delete
            Application.DisplayAlerts = True
        End If
    Next
    orig.Select
    Sht1.Activate
    Sht1.Cells(1).Select
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True

Open in new window

Roy Cox

Keep a backup file.

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Jerry Paladino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Berke

ASKER

My table did not have subtotals showing, which may be why I didn't think of double clicking a grand total.
But, thanks to Jerry, I made them reappear by selecting an cell in the table > design > grand totals > on for rows and columns. The grand total reappeared, and I double clicked it to recover the source.

Much easier than my vba cludge.

Rob Henson had the right answer and got it first, but Jerry's was more thorough, so I am giving him the Best Answer. Thanks to you both.

rberke

Jerry Paladino

Thank you Sir. Appreciate the comments.
Jerry

Rob Henson

Appreciated