Unable to get the PivotTable property of the Worksheet Class error

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!

LVL 13
Shanan212Asked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
Well, I don't see any Subbill No. field in your pivot data, that's probably your first issue.
0
 
nutschCommented:
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
 
Shanan212Author Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nutschCommented:
confirming the obvious, your pivot PPMain is on the sheet "Main Page", isn't it?
0
 
Shanan212Author Commented:
Yes it is. It refreshes fine, etc and when I check data source it points to Ranges table
0
 
nutschCommented:
Could you load a sample file, after scrambling any personal / confidential data?

Thanks,

T
0
 
Shanan212Author Commented:
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
 
Shanan212Author Commented:
I doubted everything except the input. Yes the input is wrong (was generated by someone else)

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.