Hello Excellers and welcome back for another #Excel #MacroMonday blog post in my Excel 2020 series. This small simple Excel VBA macro will allow you to quickly enable or disable the GetPivotData function in Excel.
The GetPivotData Function.
The Excel GETPIVOTDATA function can query a Pivot Table and retrieve specific data based on the Pivot Table structure, instead of cell references.
Syntax
There are three parameters or arguments that are used in GetPivotData.
- Data_field: Name or value that you are looking for.
- Pivot_Table: Any cell reference or a range of cells that is in the pivot table.
- [field1],[item1],[field2],[item2],etc: Up to 126 pairs of fields and item names can be found. This is an optional part of getpivotdata.
You can see a quick simple example below. I have used the function to return the Total Sales in all months.
Want To Read My Blog Post On GetPivotData?.
GETPIVOTDATA when you don’t want to?….(Opens in a new browser tab)
Manually Changing The GetPivotData Setting
You can change the default Excel setting manually with the steps below.
- Select the Pivot Table Analyse Tab
- Pivot Table Tools
- Options | Untick the Generate GetPivotData
But, why go through all that when we can write a simple macro to do all that with the click of a mouse?.
Starting The VBA 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.
Before you begin to write any code you need to decide where to store the code.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What Is The Difference In These Locations?.
Well, if you save the code 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. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb. This is a very useful macro which can be used over and over again. I therefore still want to save it in my personal macro workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Preparing To Write The Code.
First, I need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro.
For the first Macro, we will write two today s to disable the GetPivotData setting, and one to enable it again.
[stextbox id=’info’]
Sub deactivateGetPivotData()
End Sub [/stextbox]
The GenerateGetPivotData Property.
The next bit of code will change the GenerateGetPivotData property. By default, this is set to TRUE. In this macro, we set it to FALSE.
[stextbox id=’info’]
Application.GenerateGetPivotData = False
[/stextbox]
Ending The Macro.
Once the setting has been changed the macro ends with the End Sub statement. This was already input by default by Excel when we began writing the macro.
[stextbox id=’info’]
End Sub
[/stextbox]
Reversing Application.GenerateGetPivotData
Next, we will reverse the first macro and write another to I have called it activateGetPivotData. All I have done in this instance is changed the one line of which controls the GenerateGetPivotData from FALSE to TRUE.
[stextbox id=’info’]
Application.GenerateGetPivotData = True
[/stextbox]
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.