Pivot Macro - select absolute value

G

Guest

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").PivotFields("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.
 
G

Guest

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.
 
D

Debra Dalgleish

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
'======================
 
G

Guest

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
 
G

Guest

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"

========================================
 
D

Debra Dalgleish

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top