ExcelBanter - Pivot Macro - select absolute value

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Pivot Macro - select absolute value (https://www.excelbanter.com/excel-discussion-misc-queries/88788-pivot-macro-select-absolute-value.html)

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.

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.

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

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

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

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