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?

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?

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?

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