Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
piviot table
I have a piviot table that I inherited and to be honest I'm not very Excel
savy just yet. I have a sheet within the workbook that I need to run a macro on but when I run the macro the entire workbook recalculates each time a value on this one sheet is changed and this worksheet could possibly have 1000+ cells that need to be changed. This causes a HUGE slowdown of this macro. Here is the code that I "think" might be causing me the issue. I need to somehow exclude this one worksheet from the automatic reclaulation that the workbook is doing. I do not want to turn the auto-calculation for this workoff off. Sub ClearNoDataItems() Dim pt As PivotTable Dim PF As PivotField Dim PI As PivotItem Dim PItems As PivotItems 'Set pt = Selection.PivotTables(1) For Each pt In ActiveSheet.PivotTables pt.RefreshTable For Each PF In pt.PivotFields Set PItems = PF.PivotItems For Each PI In PItems If PI.RecordCount = 0 And PI.Name < "(blank)" Then PI.Delete Else End If Next PI Next PF Next pt End Sub Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
piviot table
Please ignore this post. I figured out the code I need.
"hshayh0rn" wrote: I have a piviot table that I inherited and to be honest I'm not very Excel savy just yet. I have a sheet within the workbook that I need to run a macro on but when I run the macro the entire workbook recalculates each time a value on this one sheet is changed and this worksheet could possibly have 1000+ cells that need to be changed. This causes a HUGE slowdown of this macro. Here is the code that I "think" might be causing me the issue. I need to somehow exclude this one worksheet from the automatic reclaulation that the workbook is doing. I do not want to turn the auto-calculation for this workoff off. Sub ClearNoDataItems() Dim pt As PivotTable Dim PF As PivotField Dim PI As PivotItem Dim PItems As PivotItems 'Set pt = Selection.PivotTables(1) For Each pt In ActiveSheet.PivotTables pt.RefreshTable For Each PF In pt.PivotFields Set PItems = PF.PivotItems For Each PI In PItems If PI.RecordCount = 0 And PI.Name < "(blank)" Then PI.Delete Else End If Next PI Next PF Next pt End Sub Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Linking table in Excel to word | Links and Linking in Excel | |||
Piviot table help | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel | |||
Lookup Table Dilemma | Excel Worksheet Functions |