Thread
:
How can I alter this Macro?
View Single Post
#
2
Posted to microsoft.public.excel.programming
Dave Peterson
external usenet poster
Posts: 35,218
How can I alter this Macro?
Untested!
Option Explicit
sub aaa()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer
set ws = worksheets("sheet1")
set pt = ws.pivottables("pivottablenamehere")
set pf = pt.pivotfields("namehere")
On Error Resume Next
For i = 1 To 2
'For Each ws In ActiveWorkbook.Worksheets
' For Each pt In ws.PivotTables
' For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
' Next
pt.RefreshTable
' Next
'Next
Next
End Sub
wrote:
All,
This is some code which I belive Debra Dalgleish supplied for me some
time back.
I am wondering how I can manipulate it for the following situations.
Only one worksheet
Only one Pivottable
Only one Field
I have tried to alter it myself but have had no luck with the nested
With's and such.
The Code:
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Integer
On Error Resume Next
For i = 1 To 2
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
For Each pf In pt.PivotFields
For Each pi In pf.PivotItems
If pi.RecordCount = 0 And _
Not pi.IsCalculated Then
pi.Delete
End If
Next
Next
pt.RefreshTable
Next
Next
Next
End Sub
--
Dave Peterson
Reply With Quote
Dave Peterson
View Public Profile
Find all posts by Dave Peterson