Help needed with Pivot table macro to remove data

[Guest]

Hi I have a macro that creates a pivot table but what I need if for each
column of data that does not include the word "Student" to be removed:

So starting at column b the first column header in the pivot table would be
in cell B3 and if this cell does not have the word Student within the text
string then that column needs to be removed!

Hope you understand? Regards Neil

 

[Debra Dalgleish]

You could loop through the column fields and their items:

Sub test()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strHide As String
Set pt = ActiveSheet.PivotTables(1)
strHide = "student"

For Each pf In pt.ColumnFields
Debug.Print pf.Name
For Each pi In pf.PivotItems
Debug.Print pi.Name
If InStr(1, UCase(pi.Caption), UCase(strHide)) > 0 Then
pi.Visible = False
Else
pi.Visible = True
End If
Next pi
Next pf


End Sub