asked on

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!

nutsch

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

Shanan212

ASKER

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

nutsch

confirming the obvious, your pivot PPMain is on the sheet "Main Page", isn't it?

Shanan212

ASKER

Yes it is. It refreshes fine, etc and when I check data source it points to Ranges table

nutsch

Could you load a sample file, after scrambling any personal / confidential data?

Thanks,

T

Shanan212

ASKER

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

Sample.xls

ASKER CERTIFIED SOLUTION

nutsch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shanan212

ASKER

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

Thanks!