?
Solved

Automaticall update Pivot table with VBA

Posted on 2011-02-16
3
Medium Priority
?
571 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
[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 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 2000 total points
ID: 34911984
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
ID: 34912056
Great, thanks just what I needed!  Thanks so much!!!
0
 

Expert Comment

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

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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