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
|