Creating Pivots in Excel VBA and Create an Email

[Excel Fox]

Just before the end of each iteration, add these green line

        If pvt.DataBodyRange.Rows.Count < 2 Then
            pvt.TableRange1.EntireRow.Delete
        End If
    Next lngPivots

[aaron.mendes]

Hi - This is not happening. for e.g. in the Move Summary Pivot, if i have all "OK" and nothing as "Move Amount Mismatch", then the pivot shows with OK in the 'xlRowField' Field.

is it possible to keep just what i require in the pivot like "Move Amount Mismatch" rather than other items as False.?

.PivotItems("OK").Visible = False
         .PivotItems("(blank)").Visible = False

[aaron.mendes]

I tried doing

.PivotItems("Move Amount Mismatch").Visible = True

but this doesnt work..

[aaron.mendes]

Hi - Sorry to bother. But this is not working. can you please help.

With your above code, the pivot does not get deleted. This is where i put it

For lngPivots = 1 To 3
        With ActiveWorkbook
            lng = .Worksheets("Output").Cells(Rows.Count, 1).End(xlUp).Row + 2
            Set pvt = pvc.CreatePivotTable(TableDestination:="Output!R" & lng & "C1", TableName:="PvtCustom" & lng, DefaultVersion:=xlPivotTableVersion12)
        End With
        pvt.AddDataField pvt.PivotFields("Agreement"), "Count of Agreement", xlCount
        With pvt.PivotFields(Array("MOVE SUMMARY", "COMMENT SUMMARY", "CALL SUMMARY")(lngPivots - 1))
            .Orientation = xlRowField
            .Position = 1
            On Error Resume Next
            .PivotItems("OK").Visible = False
            .PivotItems("(blank)").Visible = False
            Err.Clear: On Error GoTo 0: On Error GoTo -1
            .Subtotals(1) = False
        End With
        With pvt.PivotFields("Counterparty Name")
            .Orientation = xlRowField
            .Position = 2
            .Subtotals(1) = False
        End With
        With pvt
            .InGridDropZones = True
            .RowAxisLayout xlTabularRow
            .TableStyle2 = "PivotStyleMedium8"
        End With
    Next lngPivots
    Application.ScreenUpdating = 1
    If pvt.DataBodyRange.Rows.Count < 2 Then
            pvt.TableRange1.EntireRow.Delete
        End If

[aaron.mendes]

i guess i know the reason why...ur code says if the pivot has less than 2 rows then delete pivot....
E.g. in the Move Summary Pivot, if i have all "OK" and nothing as "Move Amount Mismatch", then the pivot throws up all 'OK' in the Rowfield.

this makes the pivot more than two rows.

can we do something here.