Solved

Automaticall update Pivot table with VBA

Posted on 2011-02-16
3
523 Views
Last Modified: 2013-11-06
In the attached Excel file, I have two tabs.  One contains raw data, and the other contains a pivot table of the raw data.

I’m looking for an effective VBA module that will clear all filters in pivot table (ie selection criteria for column labels and row label should revert back to “Select All”) and Change Data Source range to the full range of the new RawData.

I need this to be dynamic because the number of rows in my “RawData” may vary.  Here’s a link to trigger some thought regarding this.  I appreciate any expert advice anyone can give me in accomplishing this.

Thank you!
Pivot-Example.xlsx
0
Comment
Question by:KP_SoCal
3 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
Comment Utility
See the attached code for a sample of what will refresh your data and clear the filters on your existing row and column fields. You can put this code either in one of your pages or in a separate module, and then assign the macro of your button to this routine.
Sub UpdatePivot()
Dim pt As PivotTable

Set pt = Sheets("PivotTable").PivotTables("PivotTable2")
pt.SourceData = Sheets("RawData").[A1].CurrentRegion.Address(True, True, xlR1C1, True)
pt.RefreshTable
pt.PivotFields("Item").ClearAllFilters
pt.PivotFields("ONUM").ClearAllFilters
End Sub

Open in new window

0
 

Author Closing Comment

by:KP_SoCal
Comment Utility
Great, thanks just what I needed!  Thanks so much!!!
0
 

Expert Comment

by:Rayne
Comment Utility
This is really a life saver,thank you after 3 years!!!!
Thank you
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

17 Experts available now in Live!

Get 1:1 Help Now