?
Solved

Unable to get the PivotTable property of the Worksheet Class error

Posted on 2011-10-18
8
Medium Priority
?
521 Views
Last Modified: 2012-05-12
Dim mystr As String, pt As PivotTable

Set ulist = Sheets("Paste Ulist")
Set rep = Sheets("On Time Report")
Set mp = Sheets("Main Page")
mp.Activate
    
Dim tblRng As Range
Set tblRng = ulist.Range("A1:G" & mp.Range("A" & mp.Rows.Count).End(xlUp).Row)
tblRng.Name = "Ranges"
    
ActiveSheet.PivotTables("PPMain").PivotFields("SuBbill No").PivotItems("R50").Visible = False
ActiveSheet.PivotTables("PPMain").PivotFields("SuBbill No").PivotItems("R01").Visible = False
ActiveSheet.PivotTables("PPMain").PivotFields("Destination").PivotItems("(blank)").Visible = False

Open in new window


Hi,

I have the above coding. I am getting the error on the 1st line which starts with

ActiveSheet.PivotTables(PPMain).....

I really can't find out a reason. I reset the source (which is the 'ranges' table defined above) and the ranges is an exisiting, active table.

Any help is much appreciated!

Thanks!

0
Comment
Question by:Shanan212
  • 4
  • 4
8 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 36987846
try running this code and check that your PPMain is in

sub PivotList
dim pt as pivottable, sMsg as string

for each pt in activesheet.pivottables
smsg=smsg & pt.name &", "
next

msgbox smsg

end sub

Open in new window

0
 
LVL 13

Author Comment

by:Shanan212
ID: 36987861
Sub copy()
Dim pt As PivotTable, sMsg As String
Set mp = Sheets("Main Page")

For Each pt In mp.PivotTables
sMsg = sMsg & pt.Name & ", "
Next
  
MsgBox sMsg
  
End Sub

Open in new window


I did that code, I got the msg box giving me this

PPMain,



Meanwhile, I have changed this line below (in above post) but it has not changed anything

Set tblRng = ulist.Range("A3:S" & ulist.Range("A" & ulist.Rows.Count).End(xlUp).Row - 1)

Open in new window

0
 
LVL 39

Expert Comment

by:nutsch
ID: 36987897
confirming the obvious, your pivot PPMain is on the sheet "Main Page", isn't it?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Author Comment

by:Shanan212
ID: 36987932
Yes it is. It refreshes fine, etc and when I check data source it points to Ranges table
0
 
LVL 39

Expert Comment

by:nutsch
ID: 36987970
Could you load a sample file, after scrambling any personal / confidential data?

Thanks,

T
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36988011
File attached. I tried to run the macro from VB and corrected errors (due to making sample); name of function is 'Copy'

Sample.xls
0
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 36988139
Well, I don't see any Subbill No. field in your pivot data, that's probably your first issue.
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36988359
I doubted everything except the input. Yes the input is wrong (was generated by someone else)

Thanks!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question