Where to input a SUB function?
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?? |
Where to input a SUB function?
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?? |
Where to input a SUB function?
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?? |
Where to input a SUB function?
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? |
Where to input a SUB function?
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? |
Where to input a SUB function?
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? |
Where to input a SUB function?
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? |
Where to input a SUB function?
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? |
Where to input a SUB function?
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? |
Where to input a SUB function?
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? |
All times are GMT +1. The time now is 08:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com