Removing and Adding Pivot Table Fields
 

Hello Everyone,

I am working with VBA to create a pivot table, and have done just fine
so far. However, I need to create a two buttons that will run the
following macros:
1. A macro that will remove the selected header (either row or
column) from the pivot table.

2. A macro that will put the removed header back into the pivot
table.

I need to be able to click on the header (whether its the row or column
header) and then press the button to remove it from the table. The
second button should then add that header back into the table. My code
for creating the table is fine, I just need to work out the buttons.
Below is what I have so far. I was think that if I had variable for
the header name it would be able to tell which header to remove (so I
used Set iField = ActiveCell.Value), I also tried ActiveCell.Text. All
I need to do is put the text in the selected field into the PivotFields
range to make it hidden. However, I keep getting an error (Compile
Error: Object Required) on the line Set iField = ActiveCell.Value.
Below is my current code. Any help would be much appreciated.

Sub PivotTable()

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"'Pivot Table Data'!R1C1:R1892C7").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:="Product", _
ColumnFields:="Location"

ActiveSheet.PivotTables("PivotTable1").PivotFields ("Sales").Orientation
= _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

____________________________________________

Public Sub RemoveHeader()
Dim iField As String
Dim PT As PivotTable

Set iField = AcvtiveCell.Value

PT.PivotFields("iField").Orientation = xlHidden

End Sub
___________________________________________

Public Sub AddHeader()
Dim iField As Integer
Dim PT As PivotTable

With PT.PivotFields("iField")
If iField = "Location" Then
.Orientation = xlColumnField
Else
.Orientation = xlRowField
End If

End With

End Sub