Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Run-Time error '1004: Unable to get the PivotTables property of the worksheet class
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,979
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 1004 - unable to set the hastitle property of the axis class Henri Excel Programming 14 November 18th 07 04:21 PM
Run time error 1004 - unable to get the chartObjects property of the worksheet class hedgehog1 Excel Programming 1 April 10th 06 08:10 PM
Run Time Error 1004 Unable to set hidden property Lester Lee Excel Programming 3 July 22nd 04 03:31 AM
Run time error 1004 - Unable to get add property of the buttons class Mark[_37_] Excel Programming 0 March 1st 04 09:48 AM
Run-time error '1004' - Unable to set the Visible property of the Worksheet class Shalin Chopra Excel Programming 3 November 25th 03 08:38 PM


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement