| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Macro - select absolute value
I'm new to macros, but not excel.
I have a pivottable, and want to write a macro to select specific, absolute names of people. I record the macro, and this is what it gives me: ActiveCell.Offset(20, -12).Range("A8,A6,A3,A1").Select ActiveCell.Offset(20, -12).Range("A1").Activate Selection.Group I want it to select a person's name instead, but the code is off, but I'm close...yet so far. Example like this may help.... With ActiveSheet.PivotTables("PivotTable20").PivotField s("LO Name (f451)2") .PivotItems("SMITH, JAMIE").Visible = False .PivotItems("BENJAMIN, CHERYL").Visible = False .PivotItems("TONIS, KEN").Visible = False In the active pivottable, I want to select these three names and group them. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Macro - select absolute value
Maybe this is not helpful in your case, but the built-in function
GETPIVOTDATA can do something similar (get values from the pivot data based on "absolute" parameters) Hope this helps, Miguel. "Todd1" wrote: I'm new to macros, but not excel. I have a pivottable, and want to write a macro to select specific, absolute names of people. I record the macro, and this is what it gives me: ActiveCell.Offset(20, -12).Range("A8,A6,A3,A1").Select ActiveCell.Offset(20, -12).Range("A1").Activate Selection.Group I want it to select a person's name instead, but the code is off, but I'm close...yet so far. Example like this may help.... With ActiveSheet.PivotTables("PivotTable20").PivotField s("LO Name (f451)2") .PivotItems("SMITH, JAMIE").Visible = False .PivotItems("BENJAMIN, CHERYL").Visible = False .PivotItems("TONIS, KEN").Visible = False In the active pivottable, I want to select these three names and group them. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Macro - select absolute value
You could use something like this:
'====================================== Sub GroupPTNames() Dim pt As PivotTable Dim pf As PivotField Dim strAdd1 As String Dim strAdd2 As String Dim strAdd3 As String Dim strGroup As String Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PivotFields("Employee") strAdd1 = pf.PivotItems("Jones").LabelRange.Address strAdd2 = pf.PivotItems("Smith").LabelRange.Address strAdd3 = pf.PivotItems("Morgan").LabelRange.Address strGroup = strAdd1 & "," & strAdd2 & "," & strAdd3 Range(strGroup).Group pf.PivotItems("Jones").ParentItem.Name = "TeamA" pf.PivotItems("Jones").ParentItem.Parent.Name = "Team" End Sub '====================== Todd1 wrote: I'm new to macros, but not excel. I have a pivottable, and want to write a macro to select specific, absolute names of people. I record the macro, and this is what it gives me: ActiveCell.Offset(20, -12).Range("A8,A6,A3,A1").Select ActiveCell.Offset(20, -12).Range("A1").Activate Selection.Group I want it to select a person's name instead, but the code is off, but I'm close...yet so far. Example like this may help.... With ActiveSheet.PivotTables("PivotTable20").PivotField s("LO Name (f451)2") .PivotItems("SMITH, JAMIE").Visible = False .PivotItems("BENJAMIN, CHERYL").Visible = False .PivotItems("TONIS, KEN").Visible = False In the active pivottable, I want to select these three names and group them. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Macro - select absolute value
THanks for the advice. Wayyyy over my head.
I was hoping for some simple commands that would be more like: 1) select the pivot table 2) select the field 3) Select the 3 names in the field 4) Group the 3 names "Debra Dalgleish" wrote: You could use something like this: '====================================== Sub GroupPTNames() Dim pt As PivotTable Dim pf As PivotField Dim strAdd1 As String Dim strAdd2 As String Dim strAdd3 As String Dim strGroup As String Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PivotFields("Employee") strAdd1 = pf.PivotItems("Jones").LabelRange.Address strAdd2 = pf.PivotItems("Smith").LabelRange.Address strAdd3 = pf.PivotItems("Morgan").LabelRange.Address strGroup = strAdd1 & "," & strAdd2 & "," & strAdd3 Range(strGroup).Group pf.PivotItems("Jones").ParentItem.Name = "TeamA" pf.PivotItems("Jones").ParentItem.Parent.Name = "Team" End Sub '====================== Todd1 wrote: I'm new to macros, but not excel. I have a pivottable, and want to write a macro to select specific, absolute names of people. I record the macro, and this is what it gives me: ActiveCell.Offset(20, -12).Range("A8,A6,A3,A1").Select ActiveCell.Offset(20, -12).Range("A1").Activate Selection.Group I want it to select a person's name instead, but the code is off, but I'm close...yet so far. Example like this may help.... With ActiveSheet.PivotTables("PivotTable20").PivotField s("LO Name (f451)2") .PivotItems("SMITH, JAMIE").Visible = False .PivotItems("BENJAMIN, CHERYL").Visible = False .PivotItems("TONIS, KEN").Visible = False In the active pivottable, I want to select these three names and group them. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Macro - select absolute value
I have played and played, and got it to work! Amazing you can point someone
in the right direction and then stumble to the End Zone! Thanks. Not sure what the last item is supposed to do, as I think everything works as I hoped. What does this do: pf.PivotItems("Jones").ParentItem.Parent.Name = "Team" ======================================== "Debra Dalgleish" wrote: You could use something like this: '====================================== Sub GroupPTNames() Dim pt As PivotTable Dim pf As PivotField Dim strAdd1 As String Dim strAdd2 As String Dim strAdd3 As String Dim strGroup As String Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PivotFields("Employee") strAdd1 = pf.PivotItems("Jones").LabelRange.Address strAdd2 = pf.PivotItems("Smith").LabelRange.Address strAdd3 = pf.PivotItems("Morgan").LabelRange.Address strGroup = strAdd1 & "," & strAdd2 & "," & strAdd3 Range(strGroup).Group pf.PivotItems("Jones").ParentItem.Name = "TeamA" pf.PivotItems("Jones").ParentItem.Parent.Name = "Team" End Sub '====================== Todd1 wrote: I'm new to macros, but not excel. I have a pivottable, and want to write a macro to select specific, absolute names of people. I record the macro, and this is what it gives me: ActiveCell.Offset(20, -12).Range("A8,A6,A3,A1").Select ActiveCell.Offset(20, -12).Range("A1").Activate Selection.Group I want it to select a person's name instead, but the code is off, but I'm close...yet so far. Example like this may help.... With ActiveSheet.PivotTables("PivotTable20").PivotField s("LO Name (f451)2") .PivotItems("SMITH, JAMIE").Visible = False .PivotItems("BENJAMIN, CHERYL").Visible = False .PivotItems("TONIS, KEN").Visible = False In the active pivottable, I want to select these three names and group them. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Pivot Macro - select absolute value
Glad you got it working. The last two lines before End Sub name the new
field and the grouped item. Todd1 wrote: I have played and played, and got it to work! Amazing you can point someone in the right direction and then stumble to the End Zone! Thanks. Not sure what the last item is supposed to do, as I think everything works as I hoped. What does this do: pf.PivotItems("Jones").ParentItem.Parent.Name = "Team" ======================================== "Debra Dalgleish" wrote: You could use something like this: '====================================== Sub GroupPTNames() Dim pt As PivotTable Dim pf As PivotField Dim strAdd1 As String Dim strAdd2 As String Dim strAdd3 As String Dim strGroup As String Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PivotFields("Employee") strAdd1 = pf.PivotItems("Jones").LabelRange.Address strAdd2 = pf.PivotItems("Smith").LabelRange.Address strAdd3 = pf.PivotItems("Morgan").LabelRange.Address strGroup = strAdd1 & "," & strAdd2 & "," & strAdd3 Range(strGroup).Group pf.PivotItems("Jones").ParentItem.Name = "TeamA" pf.PivotItems("Jones").ParentItem.Parent.Name = "Team" End Sub '====================== Todd1 wrote: I'm new to macros, but not excel. I have a pivottable, and want to write a macro to select specific, absolute names of people. I record the macro, and this is what it gives me: ActiveCell.Offset(20, -12).Range("A8,A6,A3,A1").Select ActiveCell.Offset(20, -12).Range("A1").Activate Selection.Group I want it to select a person's name instead, but the code is off, but I'm close...yet so far. Example like this may help.... With ActiveSheet.PivotTables("PivotTable20").PivotField s("LO Name (f451)2") .PivotItems("SMITH, JAMIE").Visible = False .PivotItems("BENJAMIN, CHERYL").Visible = False .PivotItems("TONIS, KEN").Visible = False In the active pivottable, I want to select these three names and group them. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I can not select Pivot Table on my Data Menu | Excel Discussion (Misc queries) | |||
| macro sheet select | Excel Discussion (Misc queries) | |||
| Pivot Table Report formatting - can't select Data Source Order | Excel Discussion (Misc queries) | |||
| Spinner macro for Pivot Tables. | Excel Worksheet Functions | |||
| Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions | |||
Linear Mode
