How to write the visible PivotItems to a worksheet? 1

[feipezi]

Hi,

I tried but have some problems.

The reason is that if you check "Select Multiple Items" and select more than one item in a Pagefield, the CurrentPage of the field will show "(All)", instead of the items you selected. Of course, I understand it will be impossible to show all items selected: what if 50 items selected?

However, there must be a way of showing the items selected (visible), like writing those items to another worksheet. Because if users can only see "(All)" in the field, they won't be able to know what items visible unless they check the dropdown.

If the visible items can be written to another sheet, I will be able to set up a title for a PivotChart indicating what items the chart is about.

I tried but Excel either wrote everything to a worksheet, or nothing, never just the visible items.

Thanks in advance.

 

[SkipVought]

hi,

You know that its customary to post your code.

What did you try?

Help yourself out!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

give this a try. You could, for instance, use it directly on the sheet right next to the Page Field, assuming that you correctly identify YourSheetObject, you have ONE pivot table and one page field...

Function VisibleItems()
    Dim pvi As PivotItem
    
    For Each pvi In YourSheetObject.PivotTables(1).PageFields(1).PivotItems
        With pvi
            If .Visible Then
                VisibleItems = VisibleItems & .Value & ", "
            End If
        End With
    Next
    VisibleItems = Left(VisibleItems, Len(VisibleItems) - 2)
End Function

This function could be made to do this for ANY PT page field, with a couple of arguments and code modification.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Hi Skip,

Thanks for replying.

Sorry about not attaching the code. Here we go:


Sub GetPTFieldsItems()
Dim pt As PivotTable
Sheets("Sheet1").UsedRange.Clear
Sheets("Account Trend").Activate
Set pt = ActiveSheet.PivotTables(1)
For j = 1 To pt.PageFields.Count
Sheets("Sheet1").Cells(1, j).Value = pt.PageFields(j).Name
i = 2
For Each pti In pt.PageFields(j).PivotItems
If pti.Visible = True Then Sheets("Sheet1").Cells(i, j).Value = pti
i = i + 1
Next
Next
End Sub


The code above produces nothing but the header.

I modified your code and tried to run it but it gave me 'Run-time error 5: invalid procedure call or argument'. So I commented out the problem part and then ended up with nothing:


Function VisibleItems()
Dim pvi As PivotItem
Dim pt As PivotTable
Sheets("Account Trend").Activate
Set pt = ActiveSheet.PivotTables(1)
For Each pvi In pt.PageFields(1).PivotItems
With pvi
If .Visible Then VisibleItems = VisibleItems & .Value & ", "
End With
Next
'VisibleItems = Left(VisibleItems, Len(VisibleItems) - 2)
MsgBox VisibleItems
End Function

Sub test()
X = VisibleItems
End Sub

 

[SkipVought]

I ran VisibleItems as you posted AND uncommenting the latter statement, from text (merely adding a declaration for X, as I run Option Explicit) WITHOUT ERROR in bot cases!

Are you missing a .dll (library containing Left() & Len()) on your PC?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Again, one way that I would suggest using this function is on the sheet, right next to the Pivot Page Cell. If you Merge several cells and WRAP text, it might do the trick.

Of course, you need to delete the MsgBox statement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Hi Skip,

Thanks. I'll keep trying. BTW, I am running 2007 Excel and those functions (LEFT(), LEN()) are common and I use them quit frequently.

One more thing. I remember you told me about the way you handled Pivottables. You said you used SUMIF() combined with PT. I tried to figure out how but no luck.

Could you be more specific about how you did it?

Thanks in advance.

 

[SkipVought]

You said you used SUMIF() combined with PT.

This would have been OUTSIDE the PT, using the ROW column data as the range/criteria and the SOURCE DATA range for the SUM.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}