Changing Pivot Table Source Data

B

Barb Reinhardt

I'm trying to change the source data for a pivot table and am using this syntax

myPivot.SourceData = myString

where

mystring = 'ALL ITEMS'!all_items

I'm getting a run time error 1004, reference is not valid.

I'm not sure what the problem might be. Can someone assist?

Thanks,
Barb Reinhardt
 
P

Patrick Molloy

this works for me...

Sub set_two()
Dim pc As PivotCache
Dim pt As PivotTable
Set pt = Sheet1.PivotTables(1)
With pt
.SourceData = "'ALL ITEMS'!all_items"
.RefreshTable
End With
End Sub

i was able to raise error 1004 by using a range name that didn't exist - ie
mis-typing the name

Patrick Moloy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table VBA in Excel 2007 1
DYNAMIC RANGE FOR PIVOT TABLE 1
How do I change source data range for an existing Pivot Table? 1
Changing PivotTable Source programmatically 1
Used Range and Source data of a Pivot Table 1
Pivot table drill down sort 0
Excel 2007: "Reference is not valid" when refreshing pivot table 2
How I can set dynamic pivot table ONLY when I have enough data point 1

Top