Runtime error '1004': Unable to get the Pivot Tables propert

[susant_sabat]

Hi,

I am getting a run time error '1004': Unable to get the Pivot Tables property of the Worksheet class' while running the following code:

Sub Autpen()

Application.DisplayAlerts = True


Application.DisplayAlerts = True
ChDir "F:\Project\Workarea\Susant\Documents\Work\ADIINFDP\DSPS Reporting\#Reporting"
Workbooks.Open Filename:="F:\Project\Workarea\Susant\Documents\Work\ADIINFDP\DSPS Reporting\#Reporting\xxxDSPSprods.xls"
Cells.Select
Selection.Copy
Workbooks.Open Filename:="F:\Project\Workarea\Susant\Documents\Work\ADIINFDP\DSPS Reporting\#Reporting\DSPSprods.xls"
Sheets("DSPSprods").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Strategies by Generic").Select
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable9").RefreshTable
Sheets("DSPSprods").Select
Range("A1").Select
ActiveWorkbook.SaveAs Filename:="F:\Project\Workarea\Susant\Documents\Work\ADIINFDP\DSPS Reporting\#Reporting\DSPSprods.xls"
ChDir "F:\Project\Workarea\Susant\Documents\Work\ADIINFDP\DSPS Reporting\Diane Reports"
ActiveWorkbook.SaveAs Filename:= _
"F:\Project\Workarea\Susant\Documents\Work\ADIINFDP\DSPS Reporting\Diane Reports\DSPSprods.xls"
ActiveWorkbook.Close
ActiveWorkbook.Close

Kindly let me know if you find the reason and remedy for such an error. Any pointers will also be helpful.

 

[BrianB]

That line of code is correct.

Need to first check that the active sheet is the one containing the pivot table.

Then check the pivot table name is correct (right click the table then 'Options'.