Pivot Table Change Data Range thru VBA

Hi

I know that you can change the SQL querry for a Pivot in something like
Activesheet.pivottables(1).pivotcache.commandtext = "SELECT * FROM ....

What is the command to change the Data Range
like my pivot give me this value Sheet1!R1C1:R11C3 for SourceData
Activesheet.pivottables(1).SourceData = Sheet1!R1C1:R11C3

how can I get it to point by VBA to a different range say Sheet1!$A$1:$C$1,Sheet1!$A$3:$C$9

Tks
gowflow
LVL 31
gowflowAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
Activesheet.pivottables(1).SourceData = "Sheet1!R3C1:R9C3"

Open in new window

for example. If you have more than one pivot table based on the same pivotcache, you need to create a new cache - see sample code here: http://excelmatters.com/?p=119
0
 
Rory ArchibaldCommented:
The SourceData for a pivot table must be one contiguous range.
0
 
gowflowAuthor Commented:
then how to do it if one wants to have filtered data show in a pivot without having to everytime redesign the pivot.
gowflow
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Rory ArchibaldCommented:
You have to filter the pivot the same way as the source, or copy the filtered data to another area and use that as the source data. The former is easier. ;)
0
 
gowflowAuthor Commented:
ok suppose I copy and I have a contiguous range suppose it is
Sheet1!$A$3:$C$9

How do I programmatically now affect this new range to the existing pivot
gowflow
0
 
gowflowAuthor Commented:
Great help as usual.
Tks v much
gowflow
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.