Writing a VB macro to adjust a pivot table

m_wachsman

New Member
Joined
Feb 14, 2005
Messages
4
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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.

Code:
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

New Member
Joined
Feb 14, 2005
Messages
4
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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
Code:
Set rngFound = Worksheets("PivotGTO").Range("Pivot_Prop_IDs").Find(pItem.Value) 
pItem.Visible = rngFound.Font.Bold
Using Background Shading
Code:
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
Code:
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

New Member
Joined
Feb 14, 2005
Messages
4

ADVERTISEMENT

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

New Member
Joined
Nov 1, 2005
Messages
1
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

New Member
Joined
Feb 14, 2005
Messages
4
jobsonp said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top