Segue um exemplo:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=69
    Range("A3:J287").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Presentes!R3C1:R287C10", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Plan1!R3C1", TableName:="Tabela dinâmica1", _
        DefaultVersion:=xlPivotTableVersion14
    Sheets("Plan1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("sexo")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tabela dinâmica1").AddDataField ActiveSheet. _
        PivotTables("Tabela dinâmica1").PivotFields("nome"), "Contagem de nome", _
        xlCount
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("estado")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabela dinâmica1").PivotFields("dia")
        .Orientation = xlColumnField
        .Position = 1
    End With
End Sub