Page 1 of 3 123 LastLast
Results 1 to 10 of 25

Thread: Creating Pivots in Excel VBA and Create an Email

  1. #1
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0

    Creating Pivots in Excel VBA and Create an Email

    Hi,

    Attached is my excel data and I need to create multiple pivots on one sheet. I have Created the pivots that i require as my desired output alongwith a recorded macro.
    Can you please help me with the code or rectify the recorded code that would create a pivot for different data and ranges.
    Attached Files Attached Files
    Last edited by aaron.mendes; 07-09-2013 at 04:06 PM.

  2. #2
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    Hi - can someone please help me in this.

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Aaron, can you explain the logic for each of the pivot tables? Will have a look at it.
    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

  4. #4
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    For 1st Pivot, in the Row Labels, I require Call Summary (T) and Counterparty Name (E) and in Value, a count of Agreement (F)
    From the dropdown of Call Summary, "OK" and Blanks should be excluded.

    Other pivots have the same logic except that the column changes.

    Thanks for looking into this.

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    try this
    Code:
        
        Dim pvc As PivotCache
        Dim pvt As PivotTable
        Dim lng As Long
        Dim lngPivots As Long
        
        Application.ScreenUpdating = 0
        With ThisWorkbook
            Application.DisplayAlerts = 0
            On Error Resume Next
            .Worksheets("Output").Delete
            Err.Clear: On Error GoTo 0: On Error GoTo -1
            Application.DisplayAlerts = 1
            .Worksheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Output"
            Set pvc = .PivotCaches.Create(SourceType:=xlDatabase, SourceData:=.Worksheets("ccm_dispute_results").Cells(1).CurrentRegion.Address(, , xlR1C1, True), Version:=xlPivotTableVersion12)
        End With
        
        For lngPivots = 1 To 3
            With ThisWorkbook
                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("CALL SUMMARY", "COMMENT SUMMARY", "MOVE 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 = "PivotStyleMedium9"
            End With
        Next lngPivots
        Application.ScreenUpdating = 1
        
    End Sub
    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

  6. #6
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    I'm getting an error at tis Code sayin, SubsCript out of Range

    Code:
    Set pvc = .PivotCaches.Create(SourceType:=xlDatabase, SourceData:=.Worksheets("ccm_dispute_results").Cells(1).CurrentRegion.Address(, , xlR1C1, True), Version:=xlPivotTableVersion12)
        End With
    Last edited by Admin; 07-09-2013 at 09:51 PM. Reason: Corrected Code Tag - } to ]

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Are you sure your sheet name is correct? ie, ccm_dispute_results
    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

  8. #8
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    Yes. I used a new sheet and the Test sheet attached as well.

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Attached where?
    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

  10. #10
    Member
    Join Date
    Jul 2013
    Posts
    31
    Rep Power
    0
    The original Test file that i had attached.

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
  •