Solved

Pivot Table Change Data Range thru VBA

Posted on 2013-11-07
6
489 Views
Last Modified: 2013-11-07
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
0
Comment
Question by:gowflow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39629822
The SourceData for a pivot table must be one contiguous range.
0
 
LVL 30

Author Comment

by:gowflow
ID: 39629886
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39629919
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 30

Author Comment

by:gowflow
ID: 39630024
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 39630102
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
 
LVL 30

Author Closing Comment

by:gowflow
ID: 39630899
Great help as usual.
Tks v much
gowflow
0

Featured Post

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

691 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