We help IT Professionals succeed at work.

How to get data from Pivot into a separate sheet using VBA.

PTishyn_com
PTishyn_com asked
on
Suppose you have a pivot table.
When you click on a grand total number, details get pasted into a separate worksheet.
How do you do the same through VBA?
Comment
Watch Question

PTish.com:
See attached file and code.

Sub ShowDetailOfC9()
'This will specifically work for the Grand Total in C9 of attached file
    Range("C9").Select
    Selection.ShowDetail = True
    Range("A14").Select
End Sub
Sub ShowDetailOfActiveCell()
' In the Pivot Table, select the cell with Grand Total you want the detail for
    ActiveCell.Select
    Selection.ShowDetail = True
    ActiveCell.Select
'    Sheets("Sheet1").Select
'    ActiveCell.Select
End Sub

Open in new window

FacultyListsPivotDetail.xlsm

Author

Commented:
Laflizard,

Thank you for the code.
Is there a way to control to which sheet the output will be done ?