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

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

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

magarnagle wrote:
> 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
Contextures
http://www.contextures.com/tiptech.html

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!

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 wrote:
> 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
Contextures
http://www.contextures.com/tiptech.html

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