Hello Excellers. Welcome to another article in my #macromonday series of blog posts. Are you looking for a way to update your pivot tables without having go into each table individually?. If so, you’re in luck! In this blog post, I show you how to update multiple pivot tables at the same time.
Today, I will show you how to easily update multiple pivot tables in Excel so users are not able to show data behind the table or disable the drill down feature. Why would you want to do this. Well, this week I need to send a an Excel workbook to another colleague. The workbook contain twenty Pivot Tables. I know how to prevent drill down or disable the show detail feature. But, I heck to not want to repeat this action twenty or more times right?. Sounds like a job for an Excel macro right?
The Manual Method First!
By double-clicking on the data in the Values Area of your Pivot Table you can extract the underlying records .These are the records that make up that data. (You can also Rick- Click and hit Show Details – whatever method seems more comfortable to you).
The result of the Double – Click or Show Details will result in a new Excel Worksheet being inserted into your workbook. The workbook will contain the details of the data you drilled into. To diable this function right click into Options in the Pivot Table and deselect Enable show details.
This is not always the behaviour you want for your Pivot Tables. So, lets write the macro to disable the feature on multiple Pivot Tables
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. So, this macro would be useful to reuse in any workbook with multiple Pivot Tables. I will save it in my Personal Macro Workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then please read my additional articles below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Starting The Macro
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it Sub DisableDrillDown. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines
Sub DisableDrillDown()
End Sub
Declare Variables
Next, variables are declared if required. In this macro I declare the following variables. This is for the worksheets and the Pivot Tables in the worksheets.
Dim pvt As PivotTable
Dim sht As Worksheet
This variable creates a memory container in Excel for these values.
VBA Loop
The next part of the code uses the For Each Loop method to loop though all Excel worksheets in the workbook and all Pivot Tables in each worksheet.
For Each sht In ActiveWorkbook.Worksheets
For Each pvt In sht.PivotTables
Update Mutiple Pivot Tables
It is at this stage in the code that the PivotTable.EnableDrilldown property is change to False as each of the Pivot Table is looped.
pvt.EnableDrilldown = False
Closing The Loops, Ending The Macro
These two lines of code close the loops of each of the Pivots and worksheetsheets. once all of the drill properties of the Pivot Tables have been switched to FALSE, the looping ceases. It is at this stage the macro ends with the End Sub line of code. This code is already in the editors window as execl automatically inserts this when naming the macro.
Next pvt
Next sht
End Sub
If you want to copy the full macro code then do so below. Please save and make a backup copy of your work before running the macro.
Sub DisableDrillDown()
‘macro by how to excel at excel.com
Dim pvt As PivotTable
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
For Each pvt In sht.PivotTables
pvt.EnableDrilldown = False
Next pvt
Next sht
End Sub
That’s it! You now have a macro that will update all of your pivot tables automatically. Be sure to save the file as an Excel Macro-Enabled Workbook so that you can use it whenever you need to. And, if you want to learn more about creating and using macros, be sure to check out our other tutorials. Thanks for following along, and happy data crunching!
Even More Excel Articles.
