ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Run-Time error '1004: Unable to get the PivotTables property of the worksheet class (https://www.excelbanter.com/excel-programming/361596-run-time-error-1004-unable-get-pivottables-property-worksheet-class.html)

magarnagle
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


--
magarnagle
------------------------------------------------------------------------
magarnagle's Profile: http://www.excelforum.com/member.php...o&userid=34486
View this thread: http://www.excelforum.com/showthread...hreadid=542478

Debra Dalgleish
Run-Time error '1004: Unable to get the PivotTables propertyof the worksheet class
 
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

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

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!


--
magarnagle
------------------------------------------------------------------------
magarnagle's Profile: http://www.excelforum.com/member.php...o&userid=34486
View this thread: http://www.excelforum.com/showthread...hreadid=542478

Debra Dalgleish
Run-Time error '1004: Unable to get the PivotTables propertyof the worksheet class
 
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

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

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


--
magarnagle
------------------------------------------------------------------------
magarnagle's Profile: http://www.excelforum.com/member.php...o&userid=34486
View this thread: http://www.excelforum.com/showthread...hreadid=542478


All times are GMT +1. The time now is 01:17 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com