Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pivot Table Change Data Range thru VBA

Posted on 2013-11-07
6
Medium Priority
?
502 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 31

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 31

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 2000 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 31

Author Closing Comment

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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

604 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