Use Sample Pivot Table VBA Code

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:

1. On the Excel Ribbon, click the View tab
2. At the far right, click Macros
3. 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

_________________________