Query Regards to Adding Pivot Table fields

Question

I'm trying to automate a report creating process of which I'm stuck at a part. The code is supposed to create a pivot table and display the username in rows and count of username in value simultaneously corresponding to each other. I created the pivot table and set the username in rows by the following code.

ActiveWorkbook.PivotCaches.Create(xlDatabase, `Sheets("Days").Range("A1:B10000")).CreatePivotTable `Sheets("Days").Range("E1"), "DayData"
ActiveWorkbook.ShowPivotTableFieldList = True

With PivotFields
   With ActiveSheet.PivotTables(1).PivotFields("User Name")
        .Orientation = xlRowField
        .Position = 1
    End With

I need to know how can I make it display the count of username besides the username. Manually is can be expressed by dragging username under rows and again dragging the username under values to get count of username.

Additionally could I also enquire how do we apply filter to a pivot table

Answer 1

Here are some examples:

Private Sub DayDataPivotTable()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pCache As PivotCache
    Dim pTable As PivotTable

    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    Set pCache = wb.PivotCaches.Create(xlDatabase, ws.Range("A1:B10000"))
    Set pTable = pCache.CreatePivotTable(ws.Range("E1"), "DayData")
    wb.ShowPivotTableFieldList = True

    With pTable
        With .PivotFields("User Name")
            ' use it as row field:
            .Orientation = xlRowField
            .Position = 1

            ' use it additionally as data field:
            .Orientation = xlDataField
            .Position = 1
            .Caption = "Count of Usernames"
            .Function = xlCount
        End With

        With .RowFields(1)
            ' filter the row field:
            .ClearAllFilters
            .EnableMultiplePageItems = True
            .PivotItems("John Doe").Visible = False
            .PivotItems("(blank)").Visible = False
        End With

        With .PivotFields(2)
            ' use the second column as separate filter:
            .Orientation = xlPageField
            .Position = 1
        End With

    End With
End Sub