Have 100s of Pivot tables that organize data base on a list of companies. The list of companies if dynamic. It seems that even after a company has been removed from the data source the pivot tables still remembers the company name. These Pivot Tables have been used years and drop down list to select which companies to include in the pivot tables is becoming cumbersom to work (There are case where I have the same company spell differently or abbreviated in the list). Does any one know how to remove the old information from the hide/show list? I am in desperate need of help!!! At this point recreating all 200 Pivot Tables is not an option.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Pivot Tables "Memory" 4
- Thread starter shyam131
- Start date
JPMontreal
Programmer
Hi,
Run this macro in your workbook.
Sub RefPivot()
Dim pi As PivotCache
For Each pi In ActiveWorkbook.PivotCaches
pi.Refresh
Next pi
End Sub
Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca
Run this macro in your workbook.
Sub RefPivot()
Dim pi As PivotCache
For Each pi In ActiveWorkbook.PivotCaches
pi.Refresh
Next pi
End Sub
Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca
- Thread starter
- #3
What does this code do that the refresh button doesn't?
I ran the marco as you suggested but the original problem still remains. Is there any way to delete the pivot caches? All I want is for the pivot caches to "forget" everything but what is in the current datasource.
I ran the marco as you suggested but the original problem still remains. Is there any way to delete the pivot caches? All I want is for the pivot caches to "forget" everything but what is in the current datasource.
-
1
- #4
-
3
- #5
Actually, that is only a workaround. If you wanna do it properly, try something like this
For Each pc In ThisWorkbook.PivotCaches
pc.MissingItemsLimit = xlMissingItemsNone
Next
Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
For Each pc In ThisWorkbook.PivotCaches
pc.MissingItemsLimit = xlMissingItemsNone
Next
Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
- Thread starter
- #6
why - does it not work ???
It's there in XP but I do not have 2000 so can't tell if it is there or not....
Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
It's there in XP but I do not have 2000 so can't tell if it is there or not....
Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
Press Alt + F11 to take you to the VBE
Go Insert>Module
copy this and paste it in
sub Clean_Pivot_Data()
For Each pc In ThisWorkbook.PivotCaches
pc.MissingItemsLimit = xlMissingItemsNone
Next
end sub
Go back to excel
Go Tools>Macro>Macros
This should pop up a list of all macros in the workbook (so there should be 1 only)
Highlight the macro and click Run
et voila
Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
Go Insert>Module
copy this and paste it in
sub Clean_Pivot_Data()
For Each pc In ThisWorkbook.PivotCaches
pc.MissingItemsLimit = xlMissingItemsNone
Next
end sub
Go back to excel
Go Tools>Macro>Macros
This should pop up a list of all macros in the workbook (so there should be 1 only)
Highlight the macro and click Run
et voila
Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
xlbo,
Ok gotta admit, this is cool. Your last solution ran great!
I only had to run it once. And now everytime I refresh my chart, the old data values disappear from the drop down menus.
Ok my question is why is this script running upon refresh?
I've tested it several times and without running the macro, just changing my data and simply refreshing my charts, it's now doing the same thing as manually running the macro.
Any explanations?
tav
Ok gotta admit, this is cool. Your last solution ran great!
I only had to run it once. And now everytime I refresh my chart, the old data values disappear from the drop down menus.
Ok my question is why is this script running upon refresh?
I've tested it several times and without running the macro, just changing my data and simply refreshing my charts, it's now doing the same thing as manually running the macro.
Any explanations?
tav
What it has done is set the missingitemslimit property of your pivot tables. It will stay like that until you change it. Therefore, until it is changed, you will never have any extraneous data.
Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244
- Status
Similar threads
- Locked
- Question