software

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(quot;A8,A6,A3,A1quot;).Select
ActiveCell.Offset(20, -12).Range(quot;A1quot;).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(quot;PivotTable20quot;).PivotField s(quot;LO Name (f451)2quot;)
.PivotItems(quot;SMITH, JAMIEquot;).Visible = False
.PivotItems(quot;BENJAMIN, CHERYLquot;).Visible = False
.PivotItems(quot;TONIS, KENquot;).Visible = False

In the active pivottable, I want to select these three names and group them.

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 quot;absolutequot; parameters)

Hope this helps,
Miguel.

quot;Todd1quot; wrote:

gt; I'm new to macros, but not excel.
gt;
gt; I have a pivottable, and want to write a macro to select specific, absolute
gt; names of people. I record the macro, and this is what it gives me:
gt;
gt; ActiveCell.Offset(20, -12).Range(quot;A8,A6,A3,A1quot;).Select
gt; ActiveCell.Offset(20, -12).Range(quot;A1quot;).Activate
gt; Selection.Group
gt;
gt; I want it to select a person's name instead, but the code is off, but I'm
gt; close...yet so far. Example like this may help....
gt;
gt; With ActiveSheet.PivotTables(quot;PivotTable20quot;).PivotField s(quot;LO Name (f451)2quot;)
gt; .PivotItems(quot;SMITH, JAMIEquot;).Visible = False
gt; .PivotItems(quot;BENJAMIN, CHERYLquot;).Visible = False
gt; .PivotItems(quot;TONIS, KENquot;).Visible = False
gt;
gt; In the active pivottable, I want to select these three names and group them.
gt;
gt;
gt;
gt;
gt;
gt;

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(quot;Employeequot;)

strAdd1 = pf.PivotItems(quot;Jonesquot;).LabelRange.Address
strAdd2 = pf.PivotItems(quot;Smithquot;).LabelRange.Address
strAdd3 = pf.PivotItems(quot;Morganquot;).LabelRange.Address
strGroup = strAdd1 amp; quot;,quot; amp; strAdd2 amp; quot;,quot; amp; strAdd3

Range(strGroup).Group
pf.PivotItems(quot;Jonesquot;).ParentItem.Name = quot;TeamAquot;
pf.PivotItems(quot;Jonesquot;).ParentItem.Parent.Name = quot;Teamquot;

End Sub
'======================

Todd1 wrote:
gt; I'm new to macros, but not excel.
gt;
gt; I have a pivottable, and want to write a macro to select specific, absolute
gt; names of people. I record the macro, and this is what it gives me:
gt;
gt; ActiveCell.Offset(20, -12).Range(quot;A8,A6,A3,A1quot;).Select
gt; ActiveCell.Offset(20, -12).Range(quot;A1quot;).Activate
gt; Selection.Group
gt;
gt; I want it to select a person's name instead, but the code is off, but I'm
gt; close...yet so far. Example like this may help....
gt;
gt; With ActiveSheet.PivotTables(quot;PivotTable20quot;).PivotField s(quot;LO Name (f451)2quot;)
gt; .PivotItems(quot;SMITH, JAMIEquot;).Visible = False
gt; .PivotItems(quot;BENJAMIN, CHERYLquot;).Visible = False
gt; .PivotItems(quot;TONIS, KENquot;).Visible = False
gt;
gt; In the active pivottable, I want to select these three names and group them.
gt;
gt;
gt;
gt;
gt;
gt;--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.htmlTHanks 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

quot;Debra Dalgleishquot; wrote:

gt; You could use something like this:
gt;
gt; '======================================
gt; Sub GroupPTNames()
gt; Dim pt As PivotTable
gt; Dim pf As PivotField
gt; Dim strAdd1 As String
gt; Dim strAdd2 As String
gt; Dim strAdd3 As String
gt; Dim strGroup As String
gt;
gt; Set pt = ActiveSheet.PivotTables(1)
gt; Set pf = pt.PivotFields(quot;Employeequot;)
gt;
gt; strAdd1 = pf.PivotItems(quot;Jonesquot;).LabelRange.Address
gt; strAdd2 = pf.PivotItems(quot;Smithquot;).LabelRange.Address
gt; strAdd3 = pf.PivotItems(quot;Morganquot;).LabelRange.Address
gt; strGroup = strAdd1 amp; quot;,quot; amp; strAdd2 amp; quot;,quot; amp; strAdd3
gt;
gt; Range(strGroup).Group
gt; pf.PivotItems(quot;Jonesquot;).ParentItem.Name = quot;TeamAquot;
gt; pf.PivotItems(quot;Jonesquot;).ParentItem.Parent.Name = quot;Teamquot;
gt;
gt; End Sub
gt; '======================
gt;
gt; Todd1 wrote:
gt; gt; I'm new to macros, but not excel.
gt; gt;
gt; gt; I have a pivottable, and want to write a macro to select specific, absolute
gt; gt; names of people. I record the macro, and this is what it gives me:
gt; gt;
gt; gt; ActiveCell.Offset(20, -12).Range(quot;A8,A6,A3,A1quot;).Select
gt; gt; ActiveCell.Offset(20, -12).Range(quot;A1quot;).Activate
gt; gt; Selection.Group
gt; gt;
gt; gt; I want it to select a person's name instead, but the code is off, but I'm
gt; gt; close...yet so far. Example like this may help....
gt; gt;
gt; gt; With ActiveSheet.PivotTables(quot;PivotTable20quot;).PivotField s(quot;LO Name (f451)2quot;)
gt; gt; .PivotItems(quot;SMITH, JAMIEquot;).Visible = False
gt; gt; .PivotItems(quot;BENJAMIN, CHERYLquot;).Visible = False
gt; gt; .PivotItems(quot;TONIS, KENquot;).Visible = False
gt; gt;
gt; gt; In the active pivottable, I want to select these three names and group them.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Contextures
gt; www.contextures.com/tiptech.html
gt;
gt;

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(quot;Jonesquot;).ParentItem.Parent.Name = quot;Teamquot;

========================================
quot;Debra Dalgleishquot; wrote:

gt; You could use something like this:
gt;
gt; '======================================
gt; Sub GroupPTNames()
gt; Dim pt As PivotTable
gt; Dim pf As PivotField
gt; Dim strAdd1 As String
gt; Dim strAdd2 As String
gt; Dim strAdd3 As String
gt; Dim strGroup As String
gt;
gt; Set pt = ActiveSheet.PivotTables(1)
gt; Set pf = pt.PivotFields(quot;Employeequot;)
gt;
gt; strAdd1 = pf.PivotItems(quot;Jonesquot;).LabelRange.Address
gt; strAdd2 = pf.PivotItems(quot;Smithquot;).LabelRange.Address
gt; strAdd3 = pf.PivotItems(quot;Morganquot;).LabelRange.Address
gt; strGroup = strAdd1 amp; quot;,quot; amp; strAdd2 amp; quot;,quot; amp; strAdd3
gt;
gt; Range(strGroup).Group
gt; pf.PivotItems(quot;Jonesquot;).ParentItem.Name = quot;TeamAquot;
gt; pf.PivotItems(quot;Jonesquot;).ParentItem.Parent.Name = quot;Teamquot;
gt;
gt; End Sub
gt; '======================
gt;
gt; Todd1 wrote:
gt; gt; I'm new to macros, but not excel.
gt; gt;
gt; gt; I have a pivottable, and want to write a macro to select specific, absolute
gt; gt; names of people. I record the macro, and this is what it gives me:
gt; gt;
gt; gt; ActiveCell.Offset(20, -12).Range(quot;A8,A6,A3,A1quot;).Select
gt; gt; ActiveCell.Offset(20, -12).Range(quot;A1quot;).Activate
gt; gt; Selection.Group
gt; gt;
gt; gt; I want it to select a person's name instead, but the code is off, but I'm
gt; gt; close...yet so far. Example like this may help....
gt; gt;
gt; gt; With ActiveSheet.PivotTables(quot;PivotTable20quot;).PivotField s(quot;LO Name (f451)2quot;)
gt; gt; .PivotItems(quot;SMITH, JAMIEquot;).Visible = False
gt; gt; .PivotItems(quot;BENJAMIN, CHERYLquot;).Visible = False
gt; gt; .PivotItems(quot;TONIS, KENquot;).Visible = False
gt; gt;
gt; gt; In the active pivottable, I want to select these three names and group them.
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt; --
gt; Debra Dalgleish
gt; Contextures
gt; www.contextures.com/tiptech.html
gt;
gt;

Glad you got it working. The last two lines before End Sub name the new
field and the grouped item.

Todd1 wrote:
gt; I have played and played, and got it to work! Amazing you can point someone
gt; in the right direction and then stumble to the End Zone! Thanks.
gt;
gt; Not sure what the last item is supposed to do, as I think everything works
gt; as I hoped. What does this do:
gt;
gt; pf.PivotItems(quot;Jonesquot;).ParentItem.Parent.Name = quot;Teamquot;
gt;
gt; ========================================
gt; quot;Debra Dalgleishquot; wrote:
gt;
gt;
gt;gt;You could use something like this:
gt;gt;
gt;gt;'======================================
gt;gt;Sub GroupPTNames()
gt;gt;Dim pt As PivotTable
gt;gt;Dim pf As PivotField
gt;gt;Dim strAdd1 As String
gt;gt;Dim strAdd2 As String
gt;gt;Dim strAdd3 As String
gt;gt;Dim strGroup As String
gt;gt;
gt;gt;Set pt = ActiveSheet.PivotTables(1)
gt;gt;Set pf = pt.PivotFields(quot;Employeequot;)
gt;gt;
gt;gt;strAdd1 = pf.PivotItems(quot;Jonesquot;).LabelRange.Address
gt;gt;strAdd2 = pf.PivotItems(quot;Smithquot;).LabelRange.Address
gt;gt;strAdd3 = pf.PivotItems(quot;Morganquot;).LabelRange.Address
gt;gt;strGroup = strAdd1 amp; quot;,quot; amp; strAdd2 amp; quot;,quot; amp; strAdd3
gt;gt;
gt;gt;Range(strGroup).Group
gt;gt;pf.PivotItems(quot;Jonesquot;).ParentItem.Name = quot;TeamAquot;
gt;gt;pf.PivotItems(quot;Jonesquot;).ParentItem.Parent.Name = quot;Teamquot;
gt;gt;
gt;gt;End Sub
gt;gt;'======================
gt;gt;
gt;gt;Todd1 wrote:
gt;gt;
gt;gt;gt;I'm new to macros, but not excel.
gt;gt;gt;
gt;gt;gt;I have a pivottable, and want to write a macro to select specific, absolute
gt;gt;gt;names of people. I record the macro, and this is what it gives me:
gt;gt;gt;
gt;gt;gt; ActiveCell.Offset(20, -12).Range(quot;A8,A6,A3,A1quot;).Select
gt;gt;gt; ActiveCell.Offset(20, -12).Range(quot;A1quot;).Activate
gt;gt;gt; Selection.Group
gt;gt;gt;
gt;gt;gt;I want it to select a person's name instead, but the code is off, but I'm
gt;gt;gt;close...yet so far. Example like this may help....
gt;gt;gt;
gt;gt;gt;With ActiveSheet.PivotTables(quot;PivotTable20quot;).PivotField s(quot;LO Name (f451)2quot;)
gt;gt;gt; .PivotItems(quot;SMITH, JAMIEquot;).Visible = False
gt;gt;gt; .PivotItems(quot;BENJAMIN, CHERYLquot;).Visible = False
gt;gt;gt; .PivotItems(quot;TONIS, KENquot;).Visible = False
gt;gt;gt;
gt;gt;gt;In the active pivottable, I want to select these three names and group them.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;gt;--
gt;gt;Debra Dalgleish
gt;gt;Contextures
gt;gt;www.contextures.com/tiptech.html
gt;gt;
gt;gt;
gt;--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html