Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Editing a pivot table

    Hey,

    I'm a VBA noob. I've been able to modify some code I found to create a Pivot Table, now I just need it to go just a step or two further. Here is my code:
    Code:
    Sub NewCreatePivot()
        
        ' Creates a PivotTable report from the table on Sheet1
        ' by using the PivotTableWizard method with the PivotFields
        ' method to specify the fields in the PivotTable.
        Dim objTable As PivotTable, objField As PivotField
        
        ' Select the sheet and first cell of the table that contains the data.
        ActiveWorkbook.Sheets("Master Sheet All Countries").Select
        Range("A2").Select
        
        ' Create the PivotTable object based on the Employee data on Sheet1.
        Set objTable = ActiveSheet.PivotTableWizard
        
        ' Specify row and column fields.
        With objTable
            .PivotFields("DeptIDFltr").Orientation = xlRowField
    
            
            ' Specify a data field with its summary
            ' function and format.
            With .PivotFields("BAC FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Transitional FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Dept/Area FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("TEMP FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("On-Call FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Intern FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Contingent FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Called FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            
            With .DataPivotField
                .Orientation = xlColumnField
                .Position = 1
            End With
            
        End With
        
    End Sub
    I need to create 2 groups in the Pivot Table. Basically if the "DeptIDFltr" is "FHD", "PEF", "OGC", "SAI" or "TPL" it needs to be grouped as "HQ", everything else under DeptIDFltr should be listed as "Area". I would need to have a Sum Total for each group.

    Also, it's currently just creating a new sheet called "Sheet1", "Sheet2", etc. How can I get it to always name it "Master Pivot"?

    Thanks so much for the help!

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,503
    Thanks
    3
    Thanked 139 Times in 132 Posts
    Can you give us a sample spreadsheet that contains the fields and sample data you want to process? It is not easy for us to test and improve your code without creating a whole bunch of data that it matches.

    The easiest way to do the pivot grouping is to include a that field in your raw data.

    To rename the new sheet you can add a line after creating the objTable line as shown below.

    Set objTable = ActiveSheet.PivotTableWizard
    ActiveSheet.Name = "Master Pivot"
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Here is a sample data sheet. The DeptIDFltr won't always have all of those different variations, but I need FHD, OGC, PEF, SAI and TPL to always be grouped (there may be instances where one of those are missing as well, so the others would just need to be grouped) and then everything else will always be grouped together. I appreciate your help.

    Also, I got it to rename the sheet to "Master Pivot" and that works great, thank you. One issue, if a sheet by that name already exists it will error out. How would I get it to just override that sheet with the new data instead of erring out?

    Thanks again!
    Attached Files Attached Files

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,503
    Thanks
    3
    Thanked 139 Times in 132 Posts
    Try the following code. I think the Master Data Sheet is missing the field you need group the HQ data so I have the code duplicating that sheet and adding that field before building the Pivot Table.
    Code:
    Sub NewCreatePivot()
        
        ' Creates a PivotTable report from the table on Sheet1
        ' by using the PivotTableWizard method with the PivotFields
        ' method to specify the fields in the PivotTable.
        Dim objTable As PivotTable, objField As PivotField
        Dim strPivot As String, shtData As Worksheet, rng As Range, aCell As Range
        
        strPivot = "Master Pivot"
        Set shtData = ActiveWorkbook.Sheets("Master Data Sheet")
        
        'add FltrGroup field
        shtData.Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
        Columns("F:F").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        ActiveSheet.Range("F2").Value = "FltrGroup"
        Set rng = Range(Range("G3"), Range("G3").End(xlDown))
        For Each aCell In rng
          Select Case aCell.Value
            Case "FHD", "OGC", "PEF", "SAI", "TPL"
              aCell.Offset(0, -1).Value = "HQ"
            Case Else
              aCell.Offset(0, -1).Value = "Area"
          End Select
        Next aCell
        
        'If Master Pivot sheet exists, delete it
        If WorksheetExists(strPivot) Then
          Application.DisplayAlerts = False
          ActiveWorkbook.Sheets(strPivot).Delete
          Application.DisplayAlerts = True
        End If
        
        ' Create the PivotTable object based on the Employee data on Sheet1.
        Range("A2").Select
        Set objTable = ActiveSheet.PivotTableWizard
        ActiveSheet.Name = strPivot
        
        ' Specify row and column fields.
        With objTable
            .PivotFields("FltrGroup").Orientation = xlRowField
            .PivotFields("DeptIDFltr").Orientation = xlRowField
            
            ' Specify a data field with its summary
            ' function and format.
            With .PivotFields("BAC FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Transitional FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Dept/Area FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("TEMP FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("On-Call FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Intern FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Contingent FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .PivotFields("Called FTE")
                .Orientation = xlDataField
                .Function = xlSum
                .NumberFormat = "#,##0.00"
            End With
            
            With .DataPivotField
                .Orientation = xlColumnField
                .Position = 1
            End With
        End With
    End Sub
    
    Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
      Dim Sht As Worksheet
      WorksheetExists = False
      For Each Sht In ActiveWorkbook.Worksheets
        Debug.Print Sht.Name
        If Sht.Name = WorksheetName Then WorksheetExists = True
      Next Sht
    End Function
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    Sdizzle (2014-08-08)

  7. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    That worked like a charm! Thank you very much!

Posting Permissions

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