Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
excel_user123456
 
Posts: n/a
Default How can deleted data reappear in a refreshed pivot table in Excel

I am using Excel 2003. I have a worksheet full of data and built a pivot
table on another sheet (within the existing datasheet). Nothing too
complicated.

I updated my data by pasting new data over the top of the old data and
updated my pivot table.

The problem is that in the pivot table one of the fields still allows me to
select on data that no longer exists in the data. More specifically, I have
a column in the base data entitled "Vendors" and have approximately 20
different vendors that appear throughout the data. When I update my pivot
table, in the vendor selection list I have the current 20 vendors....fine.
However, I also have approximately 5 other vendors that were listed in old
data that are not in the current data.

How do I prevent the old data from appearing in the updated pivot table?
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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



--

Regards,

Peo Sjoblom



"excel_user123456" wrote in
message ...
I am using Excel 2003. I have a worksheet full of data and built a pivot
table on another sheet (within the existing datasheet). Nothing too
complicated.

I updated my data by pasting new data over the top of the old data and
updated my pivot table.

The problem is that in the pivot table one of the fields still allows me

to
select on data that no longer exists in the data. More specifically, I

have
a column in the base data entitled "Vendors" and have approximately 20
different vendors that appear throughout the data. When I update my pivot
table, in the vendor selection list I have the current 20 vendors....fine.
However, I also have approximately 5 other vendors that were listed in old
data that are not in the current data.

How do I prevent the old data from appearing in the updated pivot table?



  #3   Report Post  
excel_user123456
 
Posts: n/a
Default

I tried the solution you provided on the site...it didn't work. The
information directly addressed the problem...that is what I'm trying to
fix.....it just didn't fix it. I didn't try the visual basic solution as I
am not familier with VB. Any other suggestions?

"Peo Sjoblom" wrote:

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



--

Regards,

Peo Sjoblom



"excel_user123456" wrote in
message ...
I am using Excel 2003. I have a worksheet full of data and built a pivot
table on another sheet (within the existing datasheet). Nothing too
complicated.

I updated my data by pasting new data over the top of the old data and
updated my pivot table.

The problem is that in the pivot table one of the fields still allows me

to
select on data that no longer exists in the data. More specifically, I

have
a column in the base data entitled "Vendors" and have approximately 20
different vendors that appear throughout the data. When I update my pivot
table, in the vendor selection list I have the current 20 vendors....fine.
However, I also have approximately 5 other vendors that were listed in old
data that are not in the current data.

How do I prevent the old data from appearing in the updated pivot table?




  #4   Report Post  
Ken Wright
 
Posts: n/a
Default

Debra's code is very easy to use. The following is a step by step guide
that will show you what you need to do:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook

If you have named your sheets then those names will appear in the brackets
above as opposed to what you see at the moment in my note.

Right click on the where it says VBAProject(Your_Filename) and choose
'Insert Module' and it will now look like this

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

Double click the Module1 bit and then paste in the following
code starting at the Sub DeleteOldItemsWB() bit and finishing at the End Sub
bit.

Sub DeleteOldItemsWB()
'gets rid of unused items in PivotTable
' based on MSKB (202232)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

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

End Sub

Then hit File / Close and return to Microsoft Excel and save the file. Now
to run it, just do Tools / Macro / Macros / DeleteOldItemsWB


If you then want to get rid of the macro, then do the following:-

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane
you need to search for
your workbook's name, and when you find it you may need to click on the + to
expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
etc..........................
ThisWorkbook
Modules
Module1

Right click on the Module1 and select remove. When prompted with a question
re exporting, just hit no. Then hit File / Close and return to Microsoft
Excel and save the
file.

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

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Auto Update Data Source? Ket Excel Worksheet Functions 1 February 19th 05 12:14 AM
Macro does not run when data refreshed Larry Lehman Excel Discussion (Misc queries) 0 January 16th 05 08:31 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM
Pivot Tables referring to external data query Excel GuRu Excel Discussion (Misc queries) 4 December 29th 04 07:29 PM
Help with pivot charts and data labels [email protected] Charts and Charting in Excel 1 December 15th 04 04:08 PM


All times are GMT +1. The time now is 10:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"