Pivot Table Refresh

[Guest]

I have a pivot table on a template file that reads from a section of data
imported from a separate file upon each opening. This table is grouped by
Date, Transaction, and Fund. I only want 1 particular fund number to show in
this table, is there a way to save the table so it will only show this fund,
and not load in any new funds that it may see?

I tried loading in a data file that contained every possible fund,
refreshing the table selecting only the fund I wanted, and save the file, but
the next time I open it and load a new data range it shows every fund.

Thanks,
Keith

 

[Guest]

Maybe you could use an advanced filter to copy the records for only the fund
you want to see onto a new sheet, then base the pivot table off that sheet.

Or, you could create the pivot table with all the funds as you do now, then
use a macro like the following to hide all but the desired fund:

Option Explicit

'Set SelItem to the fund you want to display.
Public Const SelItem = "My Fund"

Public Sub ShowItem()
'Declare local variables
Dim ItemFound As Boolean, x As Long, pvtItm
ItemFound = False
'Make the first pivotitem visible
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Fund").PivotItems(1)
pvtItm.Visible = True
'Hide every item in the pivottable that does not
'match the selected item.
For x& = 2 To ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Fund").PivotItems.Count
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Fund").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
'selected item, hide it.
Set pvtItm = ActiveSheet. _
PivotTables("PivotTable1"). _
PivotFields("Fund").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 selected item,
'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

Copy & paste this code into a VBA module in your workbook. 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 selected item.
Finally, the first record is also hidden, unless it happens to match the
selected item. I wrote it this way because:
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.

Hope this helps,

Hutch

 

[Guest]

On your pivot table, click on the fund field arrow, and deselect all the
items, then select the one fund you would like to keep. Next time when you
close and reopen the table it will only show that one fund.

h.t.h

 

[Guest]

I was using this method, however I save the file using an empty data range,
so the 1 choice that I do want is in the list. What confuses me is that even
being refreshed on a completely empty data range, there are still choices on
the PT menus. It makes me think there is some sort of static list of names
that are saved onto the PT somewhere, is this true? Where could I find this
list to change it the way I want it?

 

[Roger Govier]

Hi

If you drag the Fund field off the PT, then Refresh and drag the field
back, it should flush the cache of fund names.

 

[Guest]

I tried refreshing without that field, I even used an empty data range, so
that there should be no funds listed. I choose to just delete and recreate
the table, it seems to be working better now. Thanks for all the help.

Keith