Copy Sheet to New Workbook & Flatten Pivot Tables

[JADownie]

Hello -

I use the below code to copy my current worksheet to a new workbook. That works fine.

I wanted to use this in another file now, but that file has Pivot Tables on the sheet. Is there an easy way to update this code to include flattening any Pivot Tables on the sheet?

Thanks for any guidance!!


Sub Copy_to_New_WB()
Sheets("Summary").Copy
End Sub

 

[jdellasala]

Once the Pivot Table is in the new workbook, and for example if the table is Date and Sales, have Date in Rows, Sum of Sales in Column and the Grand Total showing, then double click the Grand Total item and a new Worksheet will be created with ALL of the details of the original data.

I recorded selecting the Grand Total cell in a Pivot Table and then double clicking it, and this is the code that resulted:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B68").Select
    Selection.ShowDetail = True
End Sub

Cell B68 held the Grand Total of about 65 rows of annual data grouped by year of 772 rows of monthly data. Double clicking restored all 772 rows of data. And yes, I only did this with 2 columns of data, but all data will come through as it's all in the Pivot Cache.