Extract Data (double click) from Pivot Table thru VBA

[harishs]

Hi Experts,

Found this code in Google, modified as per my requirement, however, I am unable to extract data in the new sheet.

Need your help in updating the code or help me with code which satisfies the requirement.

I need to run this code to extract data from another workbook (currently, we manually double click to get data and move data to a new sheet).

Sub CopyPivotItemDataRange()
Dim wb1 As Excel.Workbook
Set wb1 = Workbooks.Open("C:\Users\Desktop\Sell 2018.xlsm", Password:="test", ReadOnly:=True)
Application.Run ("'Sell 2018.xlsm'!CODE_RUN")
Dim PvtTbl As PivotTable
Dim PvtFld As PivotField
Set PvtTbl = wb1.Sheets("Summary").PivotTables("PivotTable3")
Set PvtFld = PvtTbl.PivotFields("Month")
With PvtFld
    .PivotItems("Aug 18 Total").ShowDetail = True 'Show pivot item
    .PivotItems("Aug 18 Total").DataRange.EntireRow.Copy Destination:=Workbooks("extract.xlsm").Sheets("Sheet2").Range("A1")
End With
End Sub

My requirement is to extract data of subtotal of a month specified in the code.

Regards,
Harish S

 

[harishs]

Any Suggestions or Solutions to this query?