Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / June 2007

Tip: Looking for answers? Try searching our database.

Pivot Table Refresh

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Co9ug9ar - 12 Jun 2007 21:11 GMT
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
Tom Hutchins - 12 Jun 2007 23:11 GMT
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

> 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
[quoted text clipped - 8 lines]
> Thanks,
> Keith
sahafi - 12 Jun 2007 23:16 GMT
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
Signature

If u change the way u look @ things, the things u look at change.

> 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
[quoted text clipped - 8 lines]
> Thanks,
> Keith
Co9ug9ar - 13 Jun 2007 19:56 GMT
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?

> 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
[quoted text clipped - 14 lines]
> > Thanks,
> > Keith
Roger Govier - 13 Jun 2007 23:13 GMT
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.

Signature

Regards

Roger Govier

>I was using this method, however I save the file using an empty data
>range,
[quoted text clipped - 34 lines]
>> > Thanks,
>> > Keith
Co9ug9ar - 14 Jun 2007 14:03 GMT
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

> Hi
>
[quoted text clipped - 39 lines]
> >> > Thanks,
> >> > Keith
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.