Writing a VB macro to adjust a pivot table

[m_wachsman]

I have the following dilemma:

I have a dynamic range called Pivot_Prop_IDs, which contains numbers.
There are two pivot tables contained in the sheets called PivotGTO and PivotSTR. One of the Pivot fields, called PROP_ID, has some or all of the numbers in the dynamic range.
I'm trying to write a macro so that the pivot table will automatically select the numbers contained in the dynamic range.

So far, what I have is:



Private Sub CommandButton2_Click()
Dim i As Variant

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

For Each i In Worksheets("PivotGTO").Range("Pivot_Prop_IDs").Cells
Sheets("PivotGTO").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PROP_ID")
.PivotItems(i.Value).Visible = True
End With
Next
For Each i In Worksheets("Weekly Select Options").Range("Pivot_Prop_IDs").Cells
Sheets("PivotSTR").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PROP_ID")
.PivotItems(i.Value).Visible = True
End With
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub



the problem with it is that when the macro runs, Excel seems to select just a few of the numbers in the range, with no rhyme nor reason (at least not to me), but it will consistently select the same numbers even if I shut down Excel and try and restart it.

Any thoughts?
Thanks!

 

[Greg Truby]

To keep it simple, I would probably attack this problem from the other direction. ("Simple" applies here if you are sure that at least one item from your list will be in the Pivot Table results.) Instead of cycling through the list and then looking for a pivot item. I would cycle through the pivot items, look for each pItem in the list and set visibility then. This avoids a bit of tom foolerie that would be needed to code into the process since you can't turn all of the pivot items invisible at once, i.e. you can't "start with a clean slate".

This is not tested, but hopefully I didn't miss the mark too far.

Private Sub CommandButton2_Click()
    Dim pItem As PivotItem, rngFound As Range
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error Resume Next
    
    For Each pItem In Worksheets("PivotGTO").PivotTables("PivotTable1").PivotFields("PROP_ID").PivotItems
        Set rngFound = Worksheets("PivotGTO").Range("Pivot_Prop_IDs").Find(pItem.Value)
        pItem.Visible = Not (rngFound Is Nothing)
        Set rngFound = Nothing
    Next pItem
    
    For Each pItem In Worksheets("PivotSTR").PivotTables("PivotTable1").PivotFields("PROP_ID").PivotItems
        Set rngFound = Worksheets("Weekly Select Options").Range("Pivot_Prop_IDs").Find(pItem.Value)
        pItem.Visible = Not (rngFound Is Nothing)
        Set rngFound = Nothing
    Next pItem

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

HTH

 

[m_wachsman]

Thank you for your help; it worked out pretty well.

A further wrinkle:

That code works really well if I want to select all potential items in that field.
If I want to select just specific items, I basically have to create two ranges:
One from which the pivot table reads off of to select all items, and then another range (which I called Prop_ID_List) from which just some are selected.

So, every time I want to select just some data points, I first have to select all of them, and then have it whittle it down to some of them.

The code ends up looking like this:



Private Sub CommandButton2_Click()


Dim pti As pivotItem
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pti In Worksheets("PivotGTO").PivotTables("PivotTable1").PivotFields("PROP_ID").PivotItems
pti.Visible = True
Next pti
For Each pti In Worksheets("PivotSTR").PivotTables("PivotTable1").PivotFields("PROP_ID").PivotItems
pti.Visible = True
Next pti

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Dim ptItem As pivotItem, rangeFound As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

For Each ptItem In Worksheets("PivotGTO").PivotTables("PivotTable1").PivotFields("PROP_ID").PivotItems
Set rangeFound = Worksheets("Weekly Select Options").Range("Prop_ID_List").Find(ptItem.Value)
ptItem.Visible = Not (rangeFound Is Nothing)
Set rangeFound = Nothing
Next ptItem

For Each ptItem In Worksheets("PivotSTR").PivotTables("PivotTable1").PivotFields("PROP_ID").PivotItems
Set rangeFound = Worksheets("Weekly Select Options").Range("Prop_ID_List").Find(ptItem.Value)
ptItem.Visible = Not (rangeFound Is Nothing)
Set rangeFound = Nothing
Next ptItem

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub






Is there any room to make this more efficient?

Any suggestions would be welcome and appreciated.

 

[Greg Truby]

For those items you want to see, you could always do something fun like: <ul>[*]Bold the font of first instance.[*]Shade the first instance red.[*]Put an "x" beside the first instance.<ul>
Then your code can just check for that:

Using Bold

Set rngFound = Worksheets("PivotGTO").Range("Pivot_Prop_IDs").Find(pItem.Value) 
pItem.Visible = rngFound.Font.Bold

Using Background Shading

Set rngFound = Worksheets("PivotGTO").Range("Pivot_Prop_IDs").Find(pItem.Value) 
pItem.Visible = (rngFound.Interior.ColorIndex = 3)

Using an "x" in the column to the left

Set rngFound = Worksheets("PivotGTO").Range("Pivot_Prop_IDs").Find(pItem.Value) 
pItem.Visible = (LCase(rngFound.Offset(,-1)) = "x")

These are just three ideas of many possibilities. Just use your imagination if you don't like these :wink:

 

[m_wachsman]

Is there any way to have a macro show all items in the pivot field?
I know Microsoft says to code it as follows:

Worksheets(1).PivotTables("Pivot1").PivotFields("Month").ShowAllItems = True

In my particular instance, the code would be

Worksheets("PivotGTO").PivotTables("Pivot1").PivotFields("PROP_ID").ShowAllItems = True



But there's a problem with that. Say I have x rows in my pivot table, the fifth of which is that month, or in my case, PROP_ID. If I use the code above, it will show each item in PROP_ID multiplied the number of rows (x) that I have in my pivot table, regardless of whether that makes sense or not.

In other words, say I have the following info:


Brand x is franchised and contains PROP_ID 1
Brand y is owned and contains PROP_ID 2
Brand z is managed and contains PROP_ID 3

If I were to make a pivot of it it would look something like this:


Brand Owner Prop_ID

x Franchise 1
y Owned 2
z Managed 3

However, if I were to use the microsoft code to show all for the prop id field, I would get a pivot table looking like



Brand Owner Prop_ID

x Franchise 1
2
3
y Owned 1
2
3
z managed 1
2
3

This is clearly ridiculous. is there any way to just have it click the show all selection and show all info just for the Prop_ID field?

 

[jobsonp]

Did you ever work out a way around this problem ("it will show each item in PROP_ID multiplied the number of rows (x) that I have in my pivot table, regardless of whether that makes sense or not. "). I have the same problem and it is going to take alot of code to get around it.

Thanks in hope !

 

[m_wachsman]

Did you ever work out a way around this problem ("it will show each item in PROP_ID multiplied the number of rows (x) that I have in my pivot table, regardless of whether that makes sense or not. "). I have the same problem and it is going to take alot of code to get around it.

Thanks in hope !

No, unfortunately.