Hey all, I need to untick the options "For empty cells show" and "Preserve cell formatting on update" for 70 pivot tables at once. The tables are all in the same sheet and the data source is the same.
I am quite good with excel but a total noob when it comes to VBA.
I'd be terribly thankful if someone could help me out with a macro to do this!
Nick
Last edited by nholtappels; 02-22-2012 at 12:03 PM.
I think this could work:
I hope it helps.
Please Login or Register to view this content.
OH and also if it goes slow, considering the amount of tables at work, try wrapping the code with "Application.ScreenUpdating = False" and then "Application.ScreenUpdating = True", and also "Application.Calculation = xlCalculationManual" and then "Application.Calculation = xlCalculationAutomatic", as in:
Please Login or Register to view this content.
To keep your format i would this
In the "Pivot Table" drop down menu, "Table Options", select/check "Preserve Formatting". This should maintain your formatting changes when you refresh data.
As to refresh your pivot and keep your formatting with a macro, i will get back to you shortly
Denis
Please always attach the sample workbook without sensitive information when asking for help
To add a module
Press Alt + F11 (this is the Visual Basic Environment)
Insert Menu, select Module
Past code there
Close Visual Basic Environment (X)
Can you attach a sample of 1 or 2 pivots with some dummy data?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Big thanks to all of you guys! Pichingualas macro was exactly what I was looking for. I have no further questions
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks