Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Automaticall update Pivot table with VBA

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

885 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