Solved

Pivot Table Change Data Range thru VBA

Posted on 2013-11-07
6
450 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
  • 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 29

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 29

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 29

Author Closing Comment

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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now