Run-Time error '1004: Unable to get the PivotTables property of the worksheet class

[magarnagle]

HI,

Im pretty new to vba, and im currently working with somebody elses
code, which is supposed to work but has decided to stop.
I get the error from the title at the highlighted code, even though the
previous code uses the same pivot table, can anybody help me with ths
please:

Sub runtables()

Sheets("Avail_earth").Select
Range("D62").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("P_avail_earth").Select
Range("D24").Select
Application.WindowState = xlNormal
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Avail_trends").Select
Sheets("Fab11_Avail_Earth").Select
Range("M24").Select
Application.WindowState = xlNormal
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Fab_11_p_avail_earth").Select
Range("D12").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Sheets("Avail_trends").Select
Sheets("E500_avail").Select
Range("D16").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("WST_TPT").Select
Range("E16").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh




Sheets("ILine_Wss_earth").Select
Range("D8").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh


Sheets("E500_Wss_earth").Select
Range("D8").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh


Sheets("WST_TPT").Select
Range("D9").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh


Sheets("Wss_earth").Select
Range("D63").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
Sheets("Wss_trends").Select


Sheets("tpt_earth").Select
Range("M20").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Fab_11_tpt_earth").Select
Range("D23").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Tpt_trends").Select
Sheets("E500_Tpt").Select
Range("E13").Select
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh


Sheets("A80_earth").Select
Range("E16").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("A80_trends").Select


End Sub

 

[Debra Dalgleish]

At which line does the code stop?
What has changed in the workbook since the last time the macro ran
successfully?

 

[magarnagle]

Sheets("ILine_Wss_earth").Select
Range("D8").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh

'Open the E500 wss earth table and refresh'
Sheets("E500_Wss_earth").Select
Range("D8").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh

'Open the WST_TPT table and refresh'
Sheets("WST_TPT").Select
Range("D9").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh


It stops at the highlighted line of code, but as tou can see for the
previous worksheets, it refreshes them wit the same pivotTable.

I thought it mite be because range D9 is an empty cell but even when i
change the range to a cell with content, it still doesnt work.

What changes in the worksheet is that eveyweek the pivotTable brings in
a new column of data for that perticukar week!

Thanks for the help!

 

[Debra Dalgleish]

I read the newsgroups in plain text, so no highlighting is visible.

If you're trying to refresh all the pivot tables, perhaps you could use
the RefreshAll method instead. For example:

ActiveWorkbook.RefreshAll

 

[magarnagle]

Sheets("ILine_Wss_earth").Select
Range("D8").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh

'Open the E500 wss earth table and refresh'
Sheets("E500_Wss_earth").Select
Range("D8").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh

'Open the WST_TPT table and refresh'
Sheets("WST_TPT").Select
Range("D9").Select
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh(error
here)

I left the previous code in to show you that it executes the same code
twice before the error occures.

I tried the Activesheet.RefreshAll, but unfortunatly I get a new
error,
Run Time error '438' Object doesnt support this property or method

So another brick wall.

Thanks for the help so far