Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Refresh
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Refresh
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 "Co9ug9ar" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Refresh
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 -- If u change the way u look @ things, the things u look at change. "Co9ug9ar" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Refresh
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? "sahafi" wrote: 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 -- If u change the way u look @ things, the things u look at change. "Co9ug9ar" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Refresh
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. -- Regards Roger Govier "Co9ug9ar" wrote in message ... 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? "sahafi" wrote: 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 -- If u change the way u look @ things, the things u look at change. "Co9ug9ar" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Refresh
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 "Roger Govier" wrote: 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. -- Regards Roger Govier "Co9ug9ar" wrote in message ... 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? "sahafi" wrote: 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 -- If u change the way u look @ things, the things u look at change. "Co9ug9ar" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refresh a new worsheet on pivot table refresh | Excel Worksheet Functions | |||
Pivot Table will not refresh | Excel Discussion (Misc queries) | |||
pivot table will not refresh | Excel Discussion (Misc queries) | |||
MY PIVOT TABLE WON'T REFRESH | Excel Discussion (Misc queries) | |||
Pivot table refresh | Excel Worksheet Functions |