How do you get a TRUE refresh of a Pivot table

Oh Pivot tables are so good and boy, do they save on calc time and size of
file!
BUT.........I notice that if some of the data it relies on is changed, by
some magic, it still remembers the old data and, that old data will also be
shown in the table unless you physically delete those references using the
drop downs.
I have found that sometimes you can clear the old references by re-running
the wizard backwards as it seems to refresh those references.  But that
doesn't work in all situations.
Is there a way to remove old references automatically AND how is it possible
that they still exist?  In fact, if I delete all the data to which the Pivot
refers and refresh the Pivot, the dropdown list still shows all the items
that used to be there!????
Rob


0
robnobel (213)
12/2/2003 11:41:30 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
465 Views

Similar Articles

[PageSpeed] 0

From a previous post of Debra Dalgleish's:-

Sub PivotTableRefresh()
'Debra Dalgleish
'gets rid of unused items in PivotTable
' based on MSKB (Q202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
   For Each pt In ws.PivotTables
     pt.RefreshTable
     For Each pf In pt.PivotFields
       For Each pi In pf.PivotItems
         If pi.RecordCount = 0 And _
           Not pi.IsCalculated Then
           pi.Delete
         End If
       Next
     Next
   Next
Next
End Sub

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
Newsgroups - Where you really can get a free lunch!!
----------------------------------------------------------------------------



"rob nobel" <robnobel@dodo.com.au> wrote in message
news:OFHP22SuDHA.2508@TK2MSFTNGP12.phx.gbl...
> Oh Pivot tables are so good and boy, do they save on calc time and size of
> file!
> BUT.........I notice that if some of the data it relies on is changed, by
> some magic, it still remembers the old data and, that old data will also be
> shown in the table unless you physically delete those references using the
> drop downs.
> I have found that sometimes you can clear the old references by re-running
> the wizard backwards as it seems to refresh those references.  But that
> doesn't work in all situations.
> Is there a way to remove old references automatically AND how is it possible
> that they still exist?  In fact, if I delete all the data to which the Pivot
> refers and refresh the Pivot, the dropdown list still shows all the items
> that used to be there!????
> Rob
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003


0
ken.wright (2489)
12/2/2003 11:46:03 PM
There are instructions here for clearing old items from the dropdown lists:

    http://www.contextures.com/xlPivot04.html


rob nobel wrote:
> Oh Pivot tables are so good and boy, do they save on calc time and size of
> file!
> BUT.........I notice that if some of the data it relies on is changed, by
> some magic, it still remembers the old data and, that old data will also be
> shown in the table unless you physically delete those references using the
> drop downs.
> I have found that sometimes you can clear the old references by re-running
> the wizard backwards as it seems to refresh those references.  But that
> doesn't work in all situations.
> Is there a way to remove old references automatically AND how is it possible
> that they still exist?  In fact, if I delete all the data to which the Pivot
> refers and refresh the Pivot, the dropdown list still shows all the items
> that used to be there!????
> Rob
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
12/2/2003 11:54:39 PM
Thanks for that Ken, it surely had me frustrated.  I would still like to
know (if you can help), why and how that data can still be there.  Without
the code you suggested, the pivot table is innacurate unless you study it to
look for errors and clean them out using the drop downs (which a lot of
people would not do).
It's puzzling that data that has been changed can still be shown, which
causes some duplication of results.
I hope this problem is addressed on later versions as mine is v2000.
Rob

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:#nFQz5SuDHA.2440@TK2MSFTNGP12.phx.gbl...
> From a previous post of Debra Dalgleish's:-
>
> Sub PivotTableRefresh()
> 'Debra Dalgleish
> 'gets rid of unused items in PivotTable
> ' based on MSKB (Q202232)
> Dim ws As Worksheet
> Dim pt As PivotTable
> Dim pf As PivotField
> Dim pi As PivotItem
> Dim i As Integer
>
> On Error Resume Next
> For Each ws In ActiveWorkbook.Worksheets
>    For Each pt In ws.PivotTables
>      pt.RefreshTable
>      For Each pf In pt.PivotFields
>        For Each pi In pf.PivotItems
>          If pi.RecordCount = 0 And _
>            Not pi.IsCalculated Then
>            pi.Delete
>          End If
>        Next
>      Next
>    Next
> Next
> End Sub
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> Newsgroups - Where you really can get a free lunch!!
> --------------------------------------------------------------------------
--
>
>
>
> "rob nobel" <robnobel@dodo.com.au> wrote in message
> news:OFHP22SuDHA.2508@TK2MSFTNGP12.phx.gbl...
> > Oh Pivot tables are so good and boy, do they save on calc time and size
of
> > file!
> > BUT.........I notice that if some of the data it relies on is changed,
by
> > some magic, it still remembers the old data and, that old data will also
be
> > shown in the table unless you physically delete those references using
the
> > drop downs.
> > I have found that sometimes you can clear the old references by
re-running
> > the wizard backwards as it seems to refresh those references.  But that
> > doesn't work in all situations.
> > Is there a way to remove old references automatically AND how is it
possible
> > that they still exist?  In fact, if I delete all the data to which the
Pivot
> > refers and refresh the Pivot, the dropdown list still shows all the
items
> > that used to be there!????
> > Rob
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003
>
>


0
robnobel (213)
12/3/2003 12:02:58 AM
Hi Ken.  Thanks again for this info from a previous post.
It seems to work quite well except the (blank) item seems to portray the sum
of the  data  that's been deleted (if all data has been deleted), even after
running this code.  This is not a huge problem but doesn't look too
professional.
In short, the (blank) item cannot be unticked if it's the only one remaining
and shows the sum total of what all the other items were.
There are 2 other problems  related to Pivot Tables which I'll post
separately.
Rob

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:#nFQz5SuDHA.2440@TK2MSFTNGP12.phx.gbl...
> From a previous post of Debra Dalgleish's:-
>
> Sub PivotTableRefresh()
> 'Debra Dalgleish
> 'gets rid of unused items in PivotTable
> ' based on MSKB (Q202232)
> Dim ws As Worksheet
> Dim pt As PivotTable
> Dim pf As PivotField
> Dim pi As PivotItem
> Dim i As Integer
>
> On Error Resume Next
> For Each ws In ActiveWorkbook.Worksheets
>    For Each pt In ws.PivotTables
>      pt.RefreshTable
>      For Each pf In pt.PivotFields
>        For Each pi In pf.PivotItems
>          If pi.RecordCount = 0 And _
>            Not pi.IsCalculated Then
>            pi.Delete
>          End If
>        Next
>      Next
>    Next
> Next
> End Sub
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                   Sys Spec - Win XP Pro /  XL 00/02/03
>
> --------------------------------------------------------------------------
--
> Newsgroups - Where you really can get a free lunch!!
> --------------------------------------------------------------------------
--
>
>
>
> "rob nobel" <robnobel@dodo.com.au> wrote in message
> news:OFHP22SuDHA.2508@TK2MSFTNGP12.phx.gbl...
> > Oh Pivot tables are so good and boy, do they save on calc time and size
of
> > file!
> > BUT.........I notice that if some of the data it relies on is changed,
by
> > some magic, it still remembers the old data and, that old data will also
be
> > shown in the table unless you physically delete those references using
the
> > drop downs.
> > I have found that sometimes you can clear the old references by
re-running
> > the wizard backwards as it seems to refresh those references.  But that
> > doesn't work in all situations.
> > Is there a way to remove old references automatically AND how is it
possible
> > that they still exist?  In fact, if I delete all the data to which the
Pivot
> > refers and refresh the Pivot, the dropdown list still shows all the
items
> > that used to be there!????
> > Rob
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003
>
>


0
robnobel (213)
12/3/2003 8:17:35 AM
Reply:

Similar Artilces:

How do I get deleted items that stay in inbox with line thru them.
I inherited a computer at work. Outlook is setup so that when I delete a message it stays in the inbox, with a line thru it. How do I get the deleted items to go automatically to the deleted item folder? "rwwhit" <rwwhit@discussions.microsoft.com> wrote in message news:7D17761E-CD1B-4D04-BACB-B0D837C4DD93@microsoft.com... >I inherited a computer at work. Outlook is setup so that when I delete a > message it stays in the inbox, with a line thru it. How do I get the deleted > items to go automatically to the deleted item folder? That's normal behavior...

ALL of my email is gone- getting error message as well
I am getting this message: The message store has been corrupted by an application external to Windows Mail. Windows Mail has been able to successfully recover from this corruption. (0x0000000) In addition ALL of my emails are gone. Any suggestions on how I can recover them? And what I should do about the message. Try running the various repair functions in the WMUtil program: http://www.oehelp.com/WMUtil If still no improvement, upgrade to Windows Live Mail: http://download.live.com/wlmail It will automatically import all your WM data. Gary VanderMolen, Microsoft MVP (Mai...

Linking Combo box to pivot table?
Hi All, I have a pivot table which has teams on the left, months on the top and a count of the calls in the data area. What I would love is that if I could have a combo box on a worksheet which when I select a team from its list it only shows me that teams data in the pivot table. Is this possible please?? -- Adam ----------- Windows 98 + Office Pro 97 You could move Team to the page area of the pivot table. Select a team, and the pivot table will only show its data. Adam wrote: > Hi All, > > I have a pivot table which has teams on the left, months on the top and a > c...

Getting a -1 Status in Disqualify Lead
I'm getting a -1 status when I try and disqualify a lead. When I click on the picklist I get an IE Script Error: 'firstChild.firstChild.children[...].offsetTop' is null or not an object My guess is that the picklist values were deleted or customized or something. Where can I edit the Disqualify - Status picklist values? Any ideas on what could be happening? Just a follow up. Hopefully it will help someone. Turns out somehow our XML file included duplicate contact sections. We exported the XML, sent it to Microsoft support, they fixed the XML file and sent it back. We imported i...

SOP History tables
We are trying to determine how some of the fields in the SOP30200 and SOP30300 tables are populated when contracts are invoiced since we are working on a conversion. The fields are: SOP30200 CUSTNMBR -we assume this is coming from the Bill To Customer Number on the Contract Header (SVC00600) PRBTADCD -we assume this is coming from the Bill To Address Code on the Contract Header (SVC00600) PRSTADCD (this is supposed to be a Ship To Address Code but we aren’t sure where it’s coming from since you don’t enter a main address code on the header – we don’t use the Sites button – most of the...

getting started #3
having trouble opening my outlook. what do i need to do to be able to use microsoft outlook. you need a profile. Control panel, mail... add a profile. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM You can access this newsgroup by visiting http://www.microsoft.com/office/community/en-us/default.mspx or point yo...

Best use of tables in forms
Hi, I have created ten tables to have in my database in order to track the following: Employee Details Emergency Contacts for Employees Cellphones and who they are assigned to Computers and who they are assigned to Training Courses that employees have attended The tables I have created are: Employees EmployeesContacts CellphoneTypes CellphoneContracts EmployeeCellphones EquipmentType Equipment EmployeesEquipment Courses EmployeeCourses Cellphone Contracts I would like to create a form for data entry that has employee details on the front page, Cellphone data on the second page, equipment ...

Getting to Outlook express from Outlook 97, for newsgroups
Hi, Ive got both Outlook 97 and Outlook2000 on different machines, along with Outlook express. Have made a shortcut button to get to news from Out2000, but don't know how to access News from within Out87. Is there a way. -- Gerry Mitas Ryemont Abbey, Leixlip, Co. Kildare Phone: 01 6104509 Fax:01 601 4716 Info@mitas.ie www.mitas.ie _______________ DISCLAIMER: This e-mail may contain proprietary information, and is covered by copyright. It may be legally privileged, and is for the intended recipient only. If you are not the intended recipient you must not use, disclose, distribute, co...

SQL Back-end / Access Front-end using linked tables????
I have a backend that contains tables that I pull Driver information, Customer information , etc. (these are MAS 200 accounting software tables that are exported nightly to the SQL backend). I also have on the backend tables that I push information to; information that we enter on the forms located on the front end. I might not have this set- up correctly, but I'm linking all tables on the back end to the Access database on the front end. When trying to set up an Auto Lookup off of a query I've created in Access it says I need to go into the table change Data Type to the Lookup wizard,...

dll not getting loaded
Hi, i have created a test application which will load the dll dynamically, i have used the function LoadLibrary() which will load the dll but as i run application dll does not get loaded it gives error 14001 some what like side by side configuration is incurrect, i am using vc++6.0 and xp with service pack2 can any body help me out in his to resolve this problem, this problem comes when we deploy it on some another system but even as i have sreated application in my system it is not geting loaded. Thanks On Jun 27, 7:51 am, himanshubag...@gmail.com wrote: > Hi, > i have create...

Names, Addresses In A Table...
I Am Putting Names And Addresses In A Table For A Directory I Am trying To Put Together, 6 Rows And 3 Columns. 18 Companies To A Page, I Will Have Approx. 300 Pages When Complete. My Question Is: Is There Any Way To Make Them Go In Alphabetical order By Name Of Company? I Am Very Greatful To Anyone Who May Be Able To Give Me Some Help. Thank You very Very Much. PS: I Am New To This Kind Of Software And Would Like To Say To MS PUBLISHER...You Are Very RUDE!! This is a multi-part message in MIME format. ------=_NextPart_000_01E6_01C43075.4641A2B0 Content-Type: text/plain; charset="Utf...

How do I get address book to include e-mail address?
When I select to: box in the new message the search box does not include the e-mail address and is not included in the address line. How do i fix that? Any chance the Contacts folder has lost it's "Show this folder as an E-mail Address Book" checkmark in Properties? http://support.microsoft.com/kb/197577/en-us "Show this folder as an e-mail Address Book" check box is dimmed in Outlook Contact folder properties Hal -- Hal Hostetler, CPBE -- hhh@kvoa.com Senior Engineer/MIS -- MS MVP-Print/Imaging -- WA7BGX http://www.kvoa.com -- "When News breaks, we fix it!...

Scaling Excel table imported into Word document
I have an Excel 2002 table that is too big when I import it into a Word 2002. I can print the table for Excel by asking it to fit on one page and the table looks good and would fit into the Word document. How do I accomplish the same thing as an import into Word? Instead of importing the table as a file. Did you try and paste the object as a bitmap? Quality is a little less but that the only format that has worked for me in the past. Regards >-----Original Message----- >I have an Excel 2002 table that is too big when I import >it into a Word 2002. I can print the table ...

Net Display should refresh even when users are logged out
Net Display freezes on whichever screen is active when a user logs out. It would be desireable for this to continue to rotate through the screens as when a site uses log off automatically after each sale we would like the display to continue to refresh and market our items and services. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-bas...

Pivot table and variance calculation
Hi there Hope someone can help me as this is bugging me for a long time and nobody in the office could help me!! Basically I have a data table which i used for pivot, i have actual, budget and % variance of actual vs budget. In the pivot table, everything is ok accept the sub variance, instead of giving a variance, in sum up all the variance and give me a total: Cost Centre Manager Actual Plan Sum of Variance £ Sum of Variance % Martin Middleton 0 0 0 0% Martin Middleton 45 33 (13) -39% Martin Middleton 27 27 0 1% John Hollis 6 6 7 1 11% David...

Can I get Windows app pixel data and click some point ?
Hi Can I get some Win32 app or control window client pixel data or draw it ? Can I use mouse click some point inside this window area ? Is there some good sample project or guide for reference ? Thank you for your teaching . You can take a look at my "ColorPicker" ("A Better ZoomIn") project. You cannot effectively draw pixels on somebody else's window. Assume this is impossible. You can use a mouse click to SEE data, or not a mouse click (see my project) Sample: see my project. It's on my MVP Tips site. joe On Sun, 19 Oct 2008 07:53:01 -0700, Kid &...

Help with leading zero getting strip by Excel when saving to a .cs
I am saving a worksheet from Excel to .csv I have a number 0000457, but when I open it up the .csv file then the zeros all goine. I can show them in Excel if I format the column as text. Need answer ASAP!! Type a ' before the number. Hope this helps; if it does, please rate my post. G.Morales. "klafert" wrote: > I am saving a worksheet from Excel to .csv I have a number 0000457, but when > I open it up the .csv file then the zeros all goine. I can show them in > Excel if I format the column as text. Need answer ASAP!! It gets stripped when opening it in e...

Inner joins and table names containing spaces
I am using the format select ... FROM ListofTests INNER JOIN ( SampleLog INNER JOIN SampleTestXref ON SampleTestXref.LabRefNo = SampleLog.LabRefNo) ON ListofTests.ID = SampleTestXref.TestID WHERE SampleTestXref.External<>False; The tables SampleLog, SampleTestXref and ListofTests are actually links to [Sample Log], [Sample Test Xref] etc but I cannot get this query to work with square brackets let alone spaces. I am using this format because I want to extend the query with an outer join and Access doesn't appear to support the (*) symbol used in Oracle for the alternative SQ...

Pivot Table and live update
Hi, is it possible to have a Pivot Table refreshed as soon as I add some data in the datasheet the Pivot Table is based on and leave this datasheet? Thx for your help, Dries. This macro will help. Private Sub Worksheet_Activate() Worksheets("Base").Range("cellule").CurrentRegion.Name = "cellule" ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:="cellule" ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh End Sub Michel Ngalula E-mail:ngalulakachika@yahoo.com >-----Original Message----- >Hi, > >is ...

Excel
We're using Outlook 2003 / Excel. The refresh data option (DATA / Refresh Data) use to work fine on my Excel documents. All of a sudden the field is now greyed out, and thus can't be selected... Anyone know why, or how to correct this? ....ok, I've figured it out. If a cell with numbers/figures is NOT selected, then the Refresh Data button will be greyed out. Once the cell is selected, all is ok! "Ivan T. Williams" <itw@discussions.microsoft.com> wrote in message news:%23C2EDJlLGHA.2696@TK2MSFTNGP14.phx.gbl... > We're using Outlook 2003 / Excel. Th...

Excel gets hosed after I look at print preview
After I look at Print Preview, when I go back into my spreadsheet, every thing just dogs up and is really slow. The only way to make it normal is to C: net stop spooler. And then it is fine, or if I re-boot. Any suggestions? Win 2000 SP 4, Office XP, did a windows update and still nothing. Joel, Turn off the display of page breaks and see what happens. Regards, Jim Cone San Francisco, CA "Joel Heroic" <anonymous@discussions.microsoft.com> wrote in message news:164601c3e046$30d14720$a301280a@phx.gbl... > After I look at Print Preview, when I go back into my > spre...

Report Writer not showing all linking tables
I am modifying the Historical Stock Status Summary by Account Report and am trying to create a relationship between IV00101 (IV_ITEM_MSTR) and IV40700 (IV_Location_SETP). The join would be on LOCNCODE. When I try to create the relationship, IV40700 (IV_Location_SETP) does not show up as a relationship to the IV00101 (IV_ITEM_MSTR). How can I get this table to show up as a related table? The locncode in IV00101 is not what you want to use. You want to join IV00102 (Item Quantity Site Master) to the IV40700. Do you have data in the IV00101.LOCNCODE f...

Can't run VB Editor in Outlook 2002; I get "Insert Outlook 2000 CD".
In Outlook 2002 (10.2627.2625), when I try to run the VB Editor, the Windows Installer pops up with: "Insert the 'Microsoft Office 2000 [sic] SR-1 Premium' disk". I don't have that CD because I'm running Office XP, and the Office XP CD is all I have. True, my PC is very old. I'm running Win 2000, and previous versions of Office (95, 2000) did run on it. I did all of the following: I spun the Office XP CD, checked "Visual Basic Scripting Support", and confirmed it is set to "Run from My Computer". I ran "Repair Office&...

Table design advice.
Need design advice. I develop many Access programs, but some units will not use Access (don't get me going on their ability and mindset) . They insist on making a simple excel ss once each year with monthly/annual totals, then use a new file for each FY. I'm trying to table all the data for use in access, since I use access to show various data summaries from many units. I don't mind so much writing code to get the info and append it to access tables so I can keep several years worth of data in one table, except some units use a FY cycle, others use a CY cycle, and ...

Merging of pivot tables
Hi, I would like to ask how can I merge 2 or more pivot tables to 1 pivot table. Thank you for help. Juraj ...