Page 3 of 3 FirstFirst 123
Results 21 to 25 of 25

Thread: Creating Pivots in Excel VBA and Create an Email

  1. #21
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Just before the end of each iteration, add these green line

    Code:
            If pvt.DataBodyRange.Rows.Count < 2 Then
                pvt.TableRange1.EntireRow.Delete
            End If
        Next lngPivots
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  2. #22
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    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.?
    Code:
    .PivotItems("OK").Visible = False
             .PivotItems("(blank)").Visible = False

  3. #23
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    I tried doing
    Code:
    .PivotItems("Move Amount Mismatch").Visible = True
    but this doesnt work..

  4. #24
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    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

    Code:
    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

  5. #25
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    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.

Similar Threads

  1. Replies: 17
    Last Post: 07-15-2013, 09:56 PM
  2. VBA Code To Email Multiple Recipients From Excel
    By cdurfey in forum Excel Help
    Replies: 4
    Last Post: 06-11-2013, 12:18 AM
  3. Replies: 2
    Last Post: 05-23-2013, 08:08 AM
  4. Replies: 2
    Last Post: 03-12-2013, 02:57 PM
  5. Creating drop-down function in excel
    By Jorrg1 in forum Excel Help
    Replies: 4
    Last Post: 01-09-2013, 01:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •