.PivotItems("(Show All)").Visible = True 1

[Stripester]

Hello I'm looking for some help with the following!

Please go slow if you know of a way to help me - I'm new to all this!

I've recorded a macro to try and automate a task.

Part of the task included 'Checking (Show All) in one of my Pivot table fields, but when I came to look at the code it listed all the items instead of just .PivotItems("(Show All)&quot.Visible = True

ie

.PivotItems("01&quot.Visible = True
.PivotItems("02&quot.Visible = True
etc.

The trouble with this is that I wanted this code to work in different workbook and "01" and "02" might not be in these other workbooks.

Is there a solution?

Stripe!

 

[SkipVought]

Hi,

In VB there are COLLECTIONS of all kinds. On a sheet ther is a PivotTables Collection. A particular PivotTable can be referenced by name or index

wsSheet.PivotTables("MyFirstOne")  'the first pivottable?
...
wsSheet.PivotTables(2)   'the second pivottable for sure

Within a PivotTable Object are many other collections - and among them is a PivotItems collection within a specific PivotField.

Not knowing the structure of your PivotTable, I'll let you in on one of my analysis techniques for VB code in general. In any sub, you can step into the sub using the Debug Step Into button. Have the Watch Window activated - View/Watch Window. In the Watch Window, ADD the PivotTable object of interest using the appropriate expression like

ActiveSheet.PivotTable(1)

Open the Object and "drill down" into the Objects/Collections within the PivotTable.

You can use this as an example of drilling down in code...

Sub cc()
    For Each ptb In ActiveSheet.PivotTables
        For Each pfd In ptb.PivotFields
            For Each pit In pfd.PivotItems
                MsgBox pfd.Value & ":" & pit.Value
            Next
        Next
    Next
End Sub

Naturally, you could code for a specific pivottable/pivotfield and then set all the pivotitems' visible properties in the pivotitems collection

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stripester]

Hi Skip,

You couldn't talk to me like I'm a small child could you - I don't really know what to do with the info you've given to me.

I've run your code and seen what it does but I can't get this to help me with my problem!

Thanks

Stripe

 

[SkipVought]

Well I need to know what PivotField you are trying to manipulate -- assuming that it's the FIRST...

For Each pit In ActiveSheet.PivotTables(1).PivotFields(1).PivotItems
  pit.Visible = True
Next

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stripester]

You beauty!

Fantastic - thankyou very much!!!!!

As mentioned the data in this field is text like 01, 02, 03 etc....

Is there a way of entering one of these references in a cell - say [p1] and then running a code that will show the matching data in the field?

ie type "02" in cell p1 - click a button to run a code that show "02" in my "REF" field.

Thanks,

Stripe

 

[SkipVought]

For Each pit In ActiveSheet.PivotTables(1).PivotFields(1).PivotItems
  With pit
    If .Value = [TName].Value Then
      MsgBox .DataRange.Cells.Value
    End If
  End With
Next

where TName is a Named Range that contains a value in the PivotField collection and .DataRange.Cells.Value is the corresponding data value.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stripester]

Sorry Skip,
You'll have to talk to me like a small child again.

I've run your code and it gives me a obscure number in the message box "1022.42"

I don't know how to alter your code to show me the data in my "REF" field that matches the data in my cell (now named TName).

Thanks

Stripe

PS The earlier bit of code stopped working when I put the field in ascending order. It works fine in manual order!

 

[SkipVought]

Certainly 1022.42 cannot be a totally obscure value. It relates to SOMETHING in your pivottable.

Tell my how your pivottable is structured - Row fields, Column fields, data fields.

Give me a sample of your source data table.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stripester]

Hi Skip,

My Rows Fields are:

Page, Item, Desc, Quantity, Unit, RATE, Ref, Markup

My Data Field is:

TOTALS

I dont have any Column fields

Ref contains references that can be anything from 01 to 60

Rather than click the arrow and select the number I'd like to select it the way I previously discribed!

Thanks,

Stripe

 

[SkipVought]

Try this...

Sub SelectRef()
    For Each pit In ActiveSheet.PivotTables(1).PivotFields("Ref").PivotItems
      With pit
          .Visible = True
      End With
    Next
    For Each pit In ActiveSheet.PivotTables(1).PivotFields("Ref").PivotItems
      With pit
        If .Value = [TName].Value Then
          .Visible = True
        Else
          .Visible = False
        End If
      End With
    Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Stripester]

Have another star because I think your great!!!!

That's even more fantastic than your last bit of help!

Cheers

Stripe

 

[SkipVought]

If you really want to make this slick, here are some things you can do.

1. Select the Ref value from a Drop Down box.
generate a list of Unique Ref Values - record a macro that uses the Advanced Filter to use the Ref COlumn ONLY as source and Copy Unique Values to Another Location (no criteria). Use that Filtered List as source for a Data/Validation dropdown.

2. Select the Ref value from a ComboBox and run the macro from the ComboBox Click event.
This one is a bit more complex. It involves inserting a ComboBox from the Control Toolbox and then using the ComboBox1_Click event to run the macro.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com