Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change a pivot table field by typing into another cell
I would like to be able to change a field selection in a row area of my pivot
table by typing into another cell outside of the pivot table. There are so many selections in the field that it would be much easier for the user to be able to type what they would like the select in that field for sorting. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change a pivot table field by typing into another cell
Hi Josh
If you add this code to the worksheet containing the PT, then as you change the value entered in cell F1, the PT will react with a change in the Row field selection to that value I chose F1 to hold the value. If you change it to a different cell, then change the value of Target.Row and Target.Column as appropriate in the first IF test. Also ensure the name of the Pivot Table agrees with yours, and set the value of myField to the name of your row field you are wanting to change. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 1 Or Target.Column < 6 Then Exit Sub Dim pi As PivotItem, pf As PivotField Application.EnableEvents = False Application.ScreenUpdating = False ' change name of Pivto Table and Pivot Field to your values on next line Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields ("myfield") With pf pf.AutoSort xlManual, pf.SourceName For Each pi In pf.PivotItems pi.Visible = True Next For Each pi In pf.PivotItems If pi.Name < Range("F1").Value Then pi.Visible = False End If Next pf.AutoSort xlAutomatic, pf.SourceName End With Application.ScreenUpdating = False Application.EnableEvents = True End Sub To use this code, right click on the sheet tab with your PTView CodeCopy above code and Paste into the white pane. -- Regards Roger Govier "Josh Johansen" wrote in message ... I would like to be able to change a field selection in a row area of my pivot table by typing into another cell outside of the pivot table. There are so many selections in the field that it would be much easier for the user to be able to type what they would like the select in that field for sorting. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change a pivot table field by typing into another cell
I am getting the following errer: Run-time error '1004':
Unable to set the Visible property of the PivotItem class "Roger Govier" wrote: Hi Josh If you add this code to the worksheet containing the PT, then as you change the value entered in cell F1, the PT will react with a change in the Row field selection to that value I chose F1 to hold the value. If you change it to a different cell, then change the value of Target.Row and Target.Column as appropriate in the first IF test. Also ensure the name of the Pivot Table agrees with yours, and set the value of myField to the name of your row field you are wanting to change. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 1 Or Target.Column < 6 Then Exit Sub Dim pi As PivotItem, pf As PivotField Application.EnableEvents = False Application.ScreenUpdating = False ' change name of Pivto Table and Pivot Field to your values on next line Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields ("myfield") With pf pf.AutoSort xlManual, pf.SourceName For Each pi In pf.PivotItems pi.Visible = True Next For Each pi In pf.PivotItems If pi.Name < Range("F1").Value Then pi.Visible = False End If Next pf.AutoSort xlAutomatic, pf.SourceName End With Application.ScreenUpdating = False Application.EnableEvents = True End Sub To use this code, right click on the sheet tab with your PTView CodeCopy above code and Paste into the white pane. -- Regards Roger Govier "Josh Johansen" wrote in message ... I would like to be able to change a field selection in a row area of my pivot table by typing into another cell outside of the pivot table. There are so many selections in the field that it would be much easier for the user to be able to type what they would like the select in that field for sorting. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User form to change manual field filter in pivot table | Excel Discussion (Misc queries) | |||
change 'count of' to 'sum of' in pivot table field | Excel Discussion (Misc queries) | |||
Permanent change of Field Settings in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Table: Remove old field cell value | Excel Discussion (Misc queries) | |||
Pivot Table Page field link to cell | Excel Worksheet Functions |