To help automate data processing using Excel VBA, one of the most useful tools to use is a PivotTable.  But you may be thinking that a PivotTable is so complicated to do by hand, that it must be very hard to do in VBA.  I'm here to show you that it really isn't that hard.  I often will use a PivotTable created using VBA to create a view on the data that allows me to use GetPivotData() calls from within the excel spreadsheet to pull summaries out of the PivotTable like I need.

Here is the data that is located on Sheet 1 that I will make the pivot table out of.

To be able to see the summary of hours by the different activities, I would solve this problem by creating a pivot table in excel that would have a row containing Activity, and the data would be a sum of Hours.

To do this in VB, I will take the data on Sheet1, and create a pivot table on Sheet2, using this code.

Sub MakePivotTable()
    Dim pt As PivotTable
    Dim strField As String
    Dim WSD As Worksheet
    Set WSD = Worksheets("Sheet1")
    Dim PTOutput As Worksheet
    Set PTOutput = Worksheets("Sheet2")
    Dim PTCache As PivotCache
    Dim PRange As Range

    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    
    ' Find the last column with data
    Dim finalCol As Long
    finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    
    ' Find the range of the data
    Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

    ' Create the pivot table
    Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
    TableName:="SamplePivot")
    
    ' Define the layout of the pivot table
    
    ' Set update to manual to avoid recomputation while laying out
    pt.ManualUpdate = True
    
    ' Set up the row fields
    pt.AddFields RowFields:=Array( _
       "Activity")

    ' Set up the data fields
    With pt.PivotFields("Hours")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With
    
  ' Now calc the pivot table
    pt.ManualUpdate = False
    
End Sub

The PivotTable that this creates is: