Solved

Pivot Table Change Data Range thru VBA

Posted on 2013-11-07
6
460 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

919 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