I found a site that shows some code for removing old entries in a PivotTable.
However, it does NOT say where to put the damn code. It's a SUB function.

Here is the code:
Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub


So, where do I input it??

Hi,

Alt+f11 to open Vb editor. Right click 'this workbook' and insert module.
Paste the code in on the right.

Mike

"NightLord" wrote:

I found a site that shows some code for removing old entries in a PivotTable.
However, it does NOT say where to put the damn code. It's a SUB function.

Here is the code:
Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub


So, where do I input it??

Alt + F11 to open VBE.

CTRL + r to open project explorer.

Right-click on your workbook/project and InsertModule

Paste into that module.

Alt + q to return to the Excel window.

Good idea to save the workbook now.

ToolsMacroMacros.

Select the macro and Run

You could assign the macro to a button or keyboard short key combo.


Gord Dibben MS Excel MVP

On Wed, 24 Sep 2008 12:51:01 -0700, NightLord
wrote:

I found a site that shows some code for removing old entries in a PivotTable.
However, it does NOT say where to put the damn code. It's a SUB function.

Here is the code:
Sub DeleteMissingItems2002All()
'prevents unused items in non-OLAP PivotTables

Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc

End Sub


So, where do I input it??

Thanks guys. HUGE help.
I've never dealt with this side of Excel before, so I was completely clueless.
Will this run on it's own, or do you have to manually run the Macro every
time?

Hi,

Event code runs in response to worksheet/book events this will have to be
initiated manually.

Mike

"NightLord" wrote:

Thanks guys. HUGE help.
I've never dealt with this side of Excel before, so I was completely clueless.
Will this run on it's own, or do you have to manually run the Macro every
time?

As written the macro will only run manually.

To make it automatic you would need to call it from some type of Event like
workbook_open or beforeclose or beforesave.

Or when the worksheet is activated or de-activated.

When would you like the code to run?


Gord

On Wed, 24 Sep 2008 15:29:01 -0700, NightLord
wrote:

Thanks guys. HUGE help.
I've never dealt with this side of Excel before, so I was completely clueless.
Will this run on it's own, or do you have to manually run the Macro every
time?

For the BeforeSave event, does that happen when you click Save? I would
assume so since the program isn't psychic and would know when you're getting
read to close out for the day.

But yea, that sounds like a winner.



"Gord Dibben" wrote:

As written the macro will only run manually.

To make it automatic you would need to call it from some type of Event like
workbook_open or beforeclose or beforesave.

Or when the worksheet is activated or de-activated.

When would you like the code to run?


Gord

On Wed, 24 Sep 2008 15:29:01 -0700, NightLord
wrote:

Thanks guys. HUGE help.
I've never dealt with this side of Excel before, so I was completely clueless.
Will this run on it's own, or do you have to manually run the Macro every
time?

Yes..........beforesave event occurs when you save the workbook.

Assuming you have the macro in a general module..........just leave it
there.

Go into the VBE and select your workbook/project.

Expand MS Excel Objects.

Double-click on Thisworkbook module then paste this into that module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Call DeleteMissingItems2002All
End Sub


Gord

On Thu, 25 Sep 2008 11:51:01 -0700, NightLord
wrote:

For the BeforeSave event, does that happen when you click Save? I would
assume so since the program isn't psychic and would know when you're getting
read to close out for the day.

But yea, that sounds like a winner.



"Gord Dibben" wrote:

As written the macro will only run manually.

To make it automatic you would need to call it from some type of Event like
workbook_open or beforeclose or beforesave.

Or when the worksheet is activated or de-activated.

When would you like the code to run?


Gord

On Wed, 24 Sep 2008 15:29:01 -0700, NightLord
wrote:

Thanks guys. HUGE help.
I've never dealt with this side of Excel before, so I was completely clueless.
Will this run on it's own, or do you have to manually run the Macro every
time?

Awesome!
You're obviously a wiz at this, so tell me if I got this right:
On Sheet1, I have a column (Column B) where my folks input the names of
people. Now because Sheet2 is tracking how many times a name appears,
spelling is extremely important. What I did was assign the first 100 Rows (on
Sheet1) to inputing the names which will be used so they can just use Select
From Drop-down, and not worry about mis-spelling anything. The problem with
this is that those names then show up during my calulations on Sheet2. I
heard there is a way to have those drop-down options come from a list
somewhere, but I never understood the process because the instructions always
seem to assume you're using more data than JUST a straight list of names.
I also tried Hiding those Rows, but the data still gets counted. Is there
someplace that explains what I'm trying to accomplish?

Assuming you are using Data Validation List dropdown menu from which to pick
the names.................

Move your 100 names from sheet2 and place tham on a new sheet.

Give that list of names a defined name under InsertNameDefine

e.g. MyNames

On sheet1 Column B select as many cells as you want to have a dropdown then
go to DataValidationAllowList

In the source dialog enter =MyNames.

The new sheet can be hidden under FormatSheetHide if you prefer.


Gord

On Thu, 25 Sep 2008 17:30:01 -0700, NightLord
wrote:

Awesome!
You're obviously a wiz at this, so tell me if I got this right:
On Sheet1, I have a column (Column B) where my folks input the names of
people. Now because Sheet2 is tracking how many times a name appears,
spelling is extremely important. What I did was assign the first 100 Rows (on
Sheet1) to inputing the names which will be used so they can just use Select
From Drop-down, and not worry about mis-spelling anything. The problem with
this is that those names then show up during my calulations on Sheet2. I
heard there is a way to have those drop-down options come from a list
somewhere, but I never understood the process because the instructions always
seem to assume you're using more data than JUST a straight list of names.
I also tried Hiding those Rows, but the data still gets counted. Is there
someplace that explains what I'm trying to accomplish?