Solved

Automaticall update Pivot table with VBA

Posted on 2011-02-16
3
558 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
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

696 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