In some of the pivot table articles that I post here, there is sample code that you can use in your own files. Sometimes I show the code sample here, and you can copy and paste it into your workbooks. Other times, I give a link to a file that you can download, and copy the code from that.
If you’re not an Excel programming expert, here are a few tips for copying the Excel VBA programming code to your workbook.
Copy Sample Code
If the code is shown in an article, you can use the Ctrl + C shortcut to copy the code. Then, go to Excel, and find the place to paste it (more information below)
For example, this short macro will show you the number of pivot caches in the active workbook. This code would be pasted into a regular code module.
Sub CountCaches() MsgBox "There are " & ActiveWorkbook.PivotCaches.Count _ & " pivot caches in the active workook." End Sub
To copy this code:
- Select all the code, from the Sub, to the End Sub lines.
- Press Ctrl + C to copy the selected code.
To paste the code into a regular code module:
- Open Excel, and open the workbook in which you want to add the code
- Press the Alt key, and tap the F11 key, to open the Visual Basic Editor
- On the VBE menu bar, click Insert, then click Module
- In the new module window, right-click where the cursor is flashing, and click Paste
Workbook and Worksheet Code
Occasionally, code goes onto a worksheet module or a workbook module, instead of a regular module. If the sample code instructions don’t say where the code should be pasted, look at the procedure name.
If the name starts with Worksheet_ or Workbook_ you should paste the code onto one of those modules.
For example, the following code automatically refreshes a pivot table, if something is changed on the data source worksheet. This code would be pasted onto the data source sheet’s code module.
Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("Product").PivotTables(1).PivotCache.Refresh End Sub
To copy this code:
- Select all the code, from the Sub, to the End Sub lines.
- Press Ctrl + C to copy the selected code.
To paste the code into a worksheet code module:
- Open Excel, and open the workbook in which you want to add the code
- Right-click the sheet tab, for the sheet where the code should run – in this example it’s the data source sheet, which is named PivotData
- In the sheet module, right-click where the cursor is flashing, and click Paste
Run the Copied Code
Worksheet or Workbook Module
If you copy code into a worksheet or workbook module, it will run automatically when a specific event occurs. In the example above, the code was named Worksheet_Change, so it will run after any change is made to a cell.
Regular Module
For code that is copied to a regular module, you can run the code from the Macro command, or add a button on the worksheet to run the code.
To run the code:
- On the Excel Ribbon, click the View tab
- At the far right, click Macros
- Select a macro in the list, and click the Run button
More Examples
For more examples of copying and running sample Excel VBA code, please visit my Contextures website. There are code copying instructions here.
You’ll also find pivot table tutorials, and sample files.
Video: Copy Code to a Workbook
To see the steps for copying code, you can watch this short video tutorial.
Or watch on YouTube: Copy Code into a Workbook
_________________________
Hi, thanks for sharing your knowledge and found your site very informative. Yet, I have not been able to overcome a challenge. Every day I increase the amount of data that needs to be analyzed by 15 pivotables in the same workbook. They all share the same cache, (I use you msgbox). how can I make the cache to include this new data?
thanks.
how do I condense a minus sign in the leftmost column of a pivot table to a plus sign to hide the detail in the right most column ?