Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Automaticall update Pivot table with VBA

Posted on 2011-02-16
3
Medium Priority
?
583 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

578 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