Pivot table double column remove

[KittyS]

Hello,

I am having some VBA trouble getting a double column removed from a pivot table. I have 2 columns like this:
Sum of NOV 2016 and Sum of NOV 2016_2. Sometimes I get a double month, so the content can vary, but the "_" is the constant.
I would like to get rid of this second column.
My code reads:
With Selection.PivotTable
Cells.Find(What:="_", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveCell.Orientation = xlHidden
End With
but that doesn't work. Is there some easy trick?

 

[Jerry Sullivan]

Hi KittyS,

1. Are you trying to remove the 2nd field from just the PivotTable report or remove it from the availabile fields in the PivotTable field list?

2. What are the corresponding base field names in the data source?

 

[KittyS]

Hi Jerry,

Thanks for trying to help me out. As a reply to your questions:
1. I am trying to remove it from the report
2. the base field (column) name is NOV 2016.

Kitty

 

[Jerry Sullivan]

Kitty, For the specific action you describe the code would look like this...

Sub HideSpecificDataField()
 With Selection.PivotTable
   .PivotFields("Sum of Nov-16_2").Orientation = xlHidden
 End With
End Sub

This code is a more generalized version that it sounds like you wanted.
It will hide all datafields that match a pattern like "*_2" or "*_#".

Sub HideDataFieldsMatchingPattern()
 '--hides data fields in selected pivottable that have
 '  names matching a specified pattern
 
 Dim pvf As PivotField
 Dim pvt As PivotTable
 Dim sErrMsg As String
 
 '--modify to match the pattern of field names to be hidden
 Const sPATTERN As String = "*_2"
 
 Application.EnableCancelKey = xlErrorHandler
 Application.EnableEvents = False
 
 On Error Resume Next
 Set pvt = Selection.PivotTable
 On Error GoTo ErrProc
 
 If pvt Is Nothing Then
   MsgBox "Select any cell on a PivotTable before running this macro."
   GoTo ExitProc
 End If
 
 For Each pvf In pvt.DataFields
   If pvf.Caption Like sPATTERN Then
      '--matches pattern - hide field
      pvf.Orientation = xlHidden
   End If
 Next pvf

ExitProc:
 On Error Resume Next
 Application.EnableEvents = True
 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc
End Sub

 

[KittyS]

Hi Jerry,

Works like a charm! Thank you so much, saves me a ton of time!!

Kitty