VBA: Looping Through A Pivot Table

This is one of my most used pieces of code, especially if I need to produce the same report for a number of different people.

Let’s pretend you have a pivot table with a filter on user and you want to give a copy of the pivot table to each user.

You could manually change the filter on the pivot table and then save the file yourself.

Or, you can use some code to look through the pivot table. You will need additional code if you want to save the output. That is covered in an additional post.

The below code shows how to look through a pivot table filter, which is in cell C3

Sub LoopPivotTable()

Dim pt As PivotTable, pi As PivotItem, pf As PivotField
Dim lLoop As Long
Dim Val As String

Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PageFields(1)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each pi In pf.PivotItems
  pf.CurrentPage = pi.Value
  Val = ActiveSheet.Range("C3").Value
  ActiveSheet.Name = Val
  lLoop = lLoop + 1
Next pi

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub