VBA PivotTable Data Selection 2

[clouddog9]

I have a pivotTable that has a pivotfield in Columns "A" and "B". The pivotdata starts in Column "C." Is there a line of code to make vba show the detail (in column C) of a specific pivot item (in either columns A or B)? I am currently using the .Find(...) to find the item (as well as application.goto to go to the found cell), then using the activecell.row and then concatenating this with "C" to get the data cell's reference then showing the detail on this cell. (See code below)

With .Columns(strColLookup)'strColLookup is either "A:A" or "B:B"
   Set rngFound = .Find(What:=strLineDescr, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
End With
If Not rngFound Is Nothing Then
     varFRow = Range(rngFound.Address).Row
     .Range("C" & varFRow).ShowDetail = True
End If

I was wondering if there was a more effecient way to do this. Thanks in advance.

 

[Gruuuu]

.Find returns a Range object.

rngFound.ShowDetail should work, assuming that's what you're trying to get.

 

[clouddog9]

Not really. I was hoping for something in the pivot table hierarchy that would allow you to not even use the .find(...) object.

 

[Gruuuu]

Ok I will confess my ignorance, what exactly is it that ShowDetail is supposed to do? Tested the code you have a few times with different situations and I don't see what it's supposed to be doing.

I guess, more importantly, what is your desired end result?

 

[clouddog9]

I ask this not as an insult, but just to make sure we are on the same page: Do you know how to us Pivot Tables? Showdetail is the equivalent of double clicking on a value in the data field of the pivot table.

My desired result is a user will type in a value (or maybe use a drop down screen - that is still in the developmental stages) on a sheet. VBA will then find that value in the pivot table (on a seperate tab) and then bring up the data that belongs to what ever the user types in. In case you might be thinkinh, the reason I don't have them use the pivot table directly is because the pivot table is hidden to eliminate somebody from messing with the formatting of the pivot table (or accidentally deleting it all together).

 

[Gruuuu]

Ah. I have an add-in which precludes the use of my double-click, so I've never see that.

Wheee. That took some digging.

Worksheets(sheet).PivotTables(1).PivotFields(col).PivotItems.Item(field_entry).ShowDetail = True (or False)

[green]'col is the FIELD LABEL, not the worksheet column. Don't know if there's a workaround for that. Maybe grab it on an INDEX(MATCH())?[/green]

 

[clouddog9]

Thanks, but after trying this code it seems that the code is trying to show the detail of the pivotitem in the pivot field not the respective data in the data field. I say this because when I run the code, it cycles through the code just fine, but no new tab opens up new worksheet appears with the data.

 

[Gruuuu]

To get the pop-out, the ShowDetail needs to be made on the datapoint. The tricky part about that is that the PivotField name is not displayed in the pivot table, but if you look at the field list, it's in there. Mine happened to be "Data2" (even though my original table data field name was Data. Maybe it's a reserve word or something. I dunno)

 

[SkipVought]

Hi,

There are often many ways of accomplishing an objective, and the determination of "a more effecient way" may reqiuire additional information.

Is there a problem with your current approch that efficiency is an issue?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

Oh, no. I see that you are right.
The pop-out doesn't happen when the code is run, but that is indeed the method to use. I even tried setting it to False first.

Ok. It's the ShowDetail method of the RANGE object you want.

In that case, the only way to get a specific cell is to Find() it, or use MATCH to get the row. There is no way that I see to return the cell address of the datapoint pivotitem.

 

[clouddog9]

@Skip: no there is nothing wrong with the current way I am using. I do have a fear that if the pivot table is compromised then the current code I am using will not work. I guess I am looking for the VBA code equivalent to "GetPivotData."

@Gruuu: I want to continue to thank you for your help. I do have something similar to what you are saying in my code.

 

[SkipVought]

all kinds of things you can do, using various properties of the PivotTable object...

dim pvi as PivotItem
with YourSheetObject.PivotTables(1).PivotFields("YourFieldName")
   'now do stuff with this field like
   for each pvi in .PivotItems

   next
end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[clouddog9]

I know how to cycle through objects in a collection, but how do you make VBA select the data in the data field for a specific pivot item?

For Example:

dim pvi as PivotItem
with YourSheetObject.PivotTables(1).PivotFields("YourFieldName")
   for each pvi in .PivotItems
      If pvi.Name = "SpecificPivotItem" Then
         'SelectDetail here
      End If
   next
end with

If you do pvi.SelectDetail = True, aren't you basically asking it to show the detail of the string of the pivot item? There reason I ask this is because I have tried this, and no data populates.

 

[Gruuuu]

clouddog, the problem is that the .ShowDetail method, when applied to PivotItems, collapses or expands the display to show/hide all of the detail.

the effect that you are trying to get, where Excel creates a new sheet with a subset of the data table, is the .ShowDetail Method of Range

Now, IF there were a way to determine the Range associated with the PivotItem, this would be gravy. But I just don't see one.

Remember, the PivotItem represents the datapoint, the actual object that the PivotTable uses to populate the spreadsheet. You would expect that the Object Model would have some sort of reference to the current range that it occupies. From what I can tell, it doesn't.

But I sure would love someone to come by and put my foot in my mouth for me!

 

[clouddog9]

I should have figured as much. Thanks both of you for your help.

 

[xlbo]

So close:

Range(Worksheets(sheet).PivotTables(1).PivotFields(col).PivotItems.Item(field_entry).DataRange.Address).ShowDetail = True

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[clouddog9]

@xlbo: That does make sense (and I am very appreciative), but let me throw in a wrench; what if the pivot table has multiple column data fields?

 

[clouddog9]

And by multiple column data fields I mean multiple column labels. (By the way I did try your option and it did work for a pivot table that only had one column label)

 

[xlbo]

Depends on how you determine which column in the data field you want to use...

If you know the column numbers / offsets, you can use:

Range(Worksheets("sheet2").PivotTables(1).PivotFields("Name").PivotItems.Item("b").DataRange.Cells(1, 2).Address).ShowDetail = True

will get the detail from the data point in the 2nd column...

You could either iterate through the columns collection of the DataRange (which seems to be just a normal range object) or if you are looking for a specific item and you know the name / value of it, you could simply find the value in the header range and use the position of that to determine the column offset of the DataRange that you need to use...



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gruuuu]

Excellently informative Geoff! Star from me.