Where to input a SUB function?

[NightLord]

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??

 

[Mike H]

Hi,

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

Mike

 

[Gord Dibben]

Alt + F11 to open VBE.

CTRL + r to open project explorer.

Right-click on your workbook/project and Insert>Module

Paste into that module.

Alt + q to return to the Excel window.

Good idea to save the workbook now.

Tools>Macro>Macros.

Select the macro and Run

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


Gord Dibben MS Excel MVP

 

[NightLord]

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?

 

[Mike H]

Hi,

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

Mike

 

[Gord Dibben]

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

 

[NightLord]

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]

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

 

[NightLord]

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?

 

[Gord Dibben]

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 Insert>Name>Define

e.g. MyNames

On sheet1 Column B select as many cells as you want to have a dropdown then
go to Data>Validation>Allow>List

In the source dialog enter =MyNames.

The new sheet can be hidden under Format>Sheet>Hide if you prefer.


Gord