Add new data field to in macro to create pivot table

[arvin]

Hello,

Currently, I am using the following code in a macro to generate a pivot table.

Sub CreateVariableUnitsPivotTableReport()
'
' CreateVariableUnitsPivotTableReport Macro
'
' Keyboard Shortcut: Ctrl+v
'
Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("variableunitsdata")
    ' Delete any prior pivot tables
    For Each PT In WSD.PivotTables
        PT.TableRange2.Clear
    Next PT
    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange)
    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
        Cells(2, FinalCol + 2), TableName:="PivotTable1")
    ' Turn off updating while building the table
    PT.ManualUpdate = True
    ' Set up the row & column fields
    PT.AddFields RowFields:=Array(" Department", " Sub Activity"), _
        ColumnFields:=" Employee"

    ' Set up the data fields
    With PT.PivotFields(" Time")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        
End With
[COLOR=red]       With PT.PivotFields(" Service Request #")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
[/COLOR]        
    End With
    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
    ' Alignment of data within pivot table
    ' Formats each field in Data Fields
    Application.ScreenUpdating = False
    ' Need to make sure selection is on
    Application.PivotTableSelection = True
    Set pvtTable = ActiveSheet.PivotTables(1)
    ' Format Employee as center-aligned
    For Each pvtField In pvtTable.PivotFields
    If pvtField = "Employee" Then
        pvtTable.PivotSelect "Employee[All]", xlLabelOnly
        Selection.HorizontalAlignment = xlCenter
        Selection.Font.ColorIndex = 5
        Selection.EntireColumn.AutoFit
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xl
        .Weight = xlThin
    End With
    Range("K19").Select
    End If
    Next pvtField
        
    For Each pvtField In pvtTable.PivotFields
    If pvtField = "Department" Then
        pvtTable.PivotSelect "Department[All]", xlLabelOnly
        Selection.HorizontalAlignment = xlLeft
        Columns("K:K").EntireColumn.AutoFit
    End If
    Next pvtField
    For Each pvtField In pvtTable.DataFields
    Select Case pvtField
    '   Format Data fields
    Case "Sum of Time"
        pvtTable.PivotSelect pvtField, xlDataOnly
        Selection.HorizontalAlignment = xlCenter
        Selection.NumberFormat = "HH:MM:SS"
        Selection.Font.ColorIndex = 0
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
        
    '   Format other fields as numbers
    Case Else
        pvtTable.PivotSelect pvtField, xlDataOnly
        Selection.NumberFormat = "# ##0"
    End Select
    Next pvtField
    
    '   Format grand totals to wrap text
    For Each pvtField In pvtTable.ColumnFields
    pvtTable.PivotSelect "'Column Grand Total'", xlDataAndLabel
    Selection.WrapText = True
    Selection.Font.ColorIndex = 3
    Next pvtField
    '   Format grand totals to wrap text
    For Each pvtField In pvtTable.RowFields
    pvtTable.PivotSelect "'Row Grand Total'", xlDataAndLabel
    Selection.WrapText = True
    Selection.Font.ColorIndex = 3
    Next pvtField
    
    '   Hide SQL Data
    Columns("A:i").Select
    Selection.EntireColumn.Hidden = True
Range("A3").Select
End Sub

The section of code in red is my attempt to add another data field to the pivot table that will give the "count of Service Request #'s". However I keep on getting a Run Time Error 1004.

Any assistance is apprecitated

Thank you in advance

 

[arvin]

Wow! ...that was easy ..

All I had to do is change ".Position = 1" to ".Position = 2" and it works!

Sweet!

With PT.PivotFields(" Service Request #")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 2        
End With