Macro (or equivalent) to display certain pivot table data

[Becky]

Here's the situation. It's hard to imagine without seeing the whole sheet,
but here goes.

Assume someone enters a product number (e.g., 1438) into cell A1. Let's say
I have a pivot table about 30 rows down that lists all the sales data YTD
(with many different product numbers). I also have "ship to locations" for
where this product is delivered and I want this displayed as well (along with
respective VOL, $, etc.)

I want to have a macro (or something equivalent) that will filter on the
pivot data, so that only the product number listed in cell A1 is displayed in
the pivot table below (with all the possible ship-to locations &
corresponding data as well). It's the equivalent of clicking on the pivot
drop-down menu and selecting the one product number.

However, sales will change the product number depending on whatever number
they want to input, so I can't have the macro specify a specific number, but
just "any number".

Hope this makes some sense.

 

[Tom Hutchins]

This macro may provide a starting point for you:

Sub ShowItem()
Dim SelItem As String, ItemFound As Boolean, pvtItm
SelItem = ActiveSheet.Range("A1").Value
ItemFound = False
For Each pvtItm In
ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").PivotItems
pvtItm.Visible = True
If pvtItm.Value = SelItem Then ItemFound = True
Next pvtItm
If ItemFound = False Then
MsgBox SelItem & " not found in pivot table"
Exit Sub
End If
For Each pvtItm In
ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").PivotItems
If pvtItm.Value = SelItem Then
pvtItm.Visible = True
Else
pvtItm.Visible = False
End If
Next pvtItm
End Sub

The 2 "For Each pvtItm" statements will have to be 'unwrapped' when you
paste them into the VBA editor in Excel.

Hope this helps,

Hutch

 

[Becky]

Ok, I'm not that adept at using the editor. Can you explain more in detail
what I need to do, especially when you said "unwrap" below?

 

[Tom Hutchins]

Sometimes a long line of code or a long comment wraps (splits into 2 lines)
when posted on the forum. That causes an error when the cose is posted into
the VBA editor. Here is a slightly revised version which should avoid this
problem (I have also added comments to the code):

Public Sub ShowItem()
'Declare local varaiables
Dim SelItem As String
Dim ItemFound As Boolean, pvtItm
'Get the text entered by the user.
SelItem = ActiveSheet.Range("A1").Value
ItemFound = False
'Make every item in the pivottable visible.
For Each pvtItm In ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Product").PivotItems
pvtItm.Visible = True
'If a pivottable item matches the user's text, set
'ItemFound to TRUE (was previously FALSE).
If pvtItm.Value = SelItem Then
ItemFound = True
End If
Next pvtItm
'If no item in the pivottable matches the user's text,
'display an error message and quit.
If ItemFound = False Then
MsgBox SelItem & " not found in pivot table"
Exit Sub
End If
'Hide every item in the pivottable that does not
'match the user's text.
For Each pvtItm In ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Product").PivotItems
If pvtItm.Value = SelItem Then
pvtItm.Visible = True
Else
pvtItm.Visible = False
End If
Next pvtItm
End Sub

To use this macro:
- right-click on any sheet tab in your workbook to open the VBA editor
- From the menu bar, select Insert >> Module
- Copy & paste the code above into the module
- From the menu bar, select Debug >> Compile VBAProject
- Save the workbook
- You might add a command button on your worksheet near the user input
cell. Right-click on the button and select 'View code'. In the Click event
subroutine that appears, add the code 'Call ShowItem', as follows:

Private Sub CommandButton1_Click()
Call ShowItem
End Sub

Save, close, and re-open your workbook. The button should work.

The macro above assumes the pivvottable is named PivotTable1. To see the
name of your pivottable, click any cell in the pivottable. Then display the
Pivot Table toolbar and select Pivot Table >> Table Options. The name of your
table is displayed and can be changed in the box that appears.

Likewise, the macro assumes the pivottable field you are trying to match is
called 'Product'. Change it to the correct field name.

Hope this helps,

Hutch

 

[Becky]

Hey Hutch,

Thanks for all the info. My boss and I were working through it this morning,
so we were able to get the macro to work. However, there's something else
we'd like to tweak. When the macro runs, we want it to find the product
number and stop at that point. It wants to run through every possible product
number so it takes a minute or two for the macro to completely run. What can
we add to the macro so that once it finds the product number we inserted into
cell A1, it stops at that point and displays the product number & the
corresponding data in the pivot table?

 

[Becky]

We still need your help Hutch. Don't forget about us.

 

[Debra Dalgleish]

In this section, add a line to exit the loop when the item is found:

If pvtItm.Value = SelItem Then
ItemFound = True
Exit For ''added this line
End If

Near the end, change the code to hide the visible items that don't match:

If pvtItm.Value <> SelItem Then
pvtItm.Visible = False ''changed this
End If

 

[Becky]

Okay. It didn't work the way we wanted it, too. Let's say we have a list of
1,000 product numbers. When we put the product # in cell A1, the code makes
the macro stop after the first cell, I believe, and returns the message
"can't be found in pivot". We want it to go down through the list, find the
product number, and display all associated with it.

In a different way, is there a way for a macro to be created that would do
this? If we entered the product # in cell A1, and then created a macro that
would, in effect, select the pivot field button "product number", and then
the macro would go down through the list of product #'s and select the number
that is displayed in cell A1.

 

[Tom Hutchins]

I didn't abandon you...I have been trying lots of possible alternative
solutions for your problem. You must have a huge pivottable if it takes that
long to run. Two things make your request more challenging:
1) You can never hide all the records in a pivot table. At least one must be
visible at all times; and
2) There is no single command to hide or unhide all the records at the same
time. You have to loop through all the items.

The last macro I posted looped through all the items twice. I have been
searching for a way to loop through only once, making sure at least one
record is always visible, and ultimately hiding all records but the desired
one. Here is my solution:

Public Sub ShowItem()
'Declare local variables
Dim SelItem As String
Dim ItemFound As Boolean, x As Long, pvtItm
'Get the text entered by the user.
SelItem$ = ActiveSheet.Range("A1").Value
ItemFound = False
'Make the first pivotitem visible
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Product").PivotItems(1)
pvtItm.Visible = True
'Hide every item in the pivottable that does not
'match the user's text.
For x& = 2 To ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Product").PivotItems.Count
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Product").PivotItems(x&)
If pvtItm = SelItem$ Then
pvtItm.Visible = True
ItemFound = True
Else
pvtItm.Visible = False
End If
Next x&
'Unless the first PivotItem matches the
'user's text, hide it.
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Product").PivotItems(1)
If pvtItm <> SelItem$ Then
If ItemFound = True Then
pvtItm.Visible = False
End If
Else
ItemFound = True
End If
'If no item in the pivottable matches the user's text,
'display an error message and quit.
If ItemFound = False Then
MsgBox SelItem$ & " not found in pivot table"
Exit Sub
End If
'Free object variables
Set pvtItm = Nothing
End Sub

This makes the first item in the pivottable visible, then loops through the
rest of the records once. Every record is hidden unless it matches the user's
input. Finally, the first record is also hidden, unless it happens to match
the user's input.

Hope this helps,

Hutch