Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem inserting calculated pivot fields into Pivot Table (2007) | Excel Discussion (Misc queries) | |||
fields names do not show in excel 2007 pivot table fields list | New Users to Excel | |||
Adding multiple fields to a Pivot table at one time | Excel Discussion (Misc queries) | |||
number of fields in the row fields in pivot table | Excel Discussion (Misc queries) | |||
removing (All) from Pivot Table | Excel Programming |