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?

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?