Pivot Table Macro - Moving Values in to Row Labels (Builder)

[JamesS]

Hello, Apologies for this being my first post and a question.

I've been trying to record a macro that has the sum values as row labels and the CloseDate as the columns. I have highlighted the code in red when I try and move the values in to the row labels.

The other aspects of the code I am unsure how to solve are that the number of dates and number pieces sold will vary but don't want the blank field in the pivot table. Is this possible?

Lastly is it possible to use certain cells of data in a pivot table on a new sheet using standard formulas?

I'm using Excel 2011 for Mac - Would it help if I uploaded the excel file?

Thank you.

Sub Test()
'
' Test Macro
'

'
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Search Results!R1C1:R1048576C6", Version:=xlPivotTableVersion14). _
        CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable3" _
        , DefaultVersion:=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("How Purchased")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Close Dt")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Tables"), "Count of Tables", xlCount
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Warranty"), "Count of Warranty", xlCount
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Chairs"), "Count of Chairs", xlCount
    ExecuteExcel4Macro _
        "PIVOT.FIELD.PROPERTIES(""PivotTable3"",""Count of Tables"",,,2)"
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Count of Chairs"), "Count of Chairs", xlCount
    ExecuteExcel4Macro _
        "PIVOT.FIELD.PROPERTIES(""PivotTable3"",""Count of Chairs"",,,2)"
    ExecuteExcel4Macro _
        "PIVOT.FIELD.PROPERTIES(""PivotTable3"",""Count of Warranty"",,,2)"
    With ActiveSheet.PivotTables("PivotTable3").PivotFields(-1)
        .Orientation = xlRowField
        .Position = 1
    End With
    Range("E7").Select
    ExecuteExcel4Macro _
        "(""PivotTable3"",1,""%warranty/tables"",""=Warranty /Tables"",TRUE)"
    ActiveSheet.PivotTables("PivotTable3").PivotFields("%warranty/tables"). _
        Orientation = xlDataField
    Rows("8:8").Select
    Selection.Style = "Percent"
    Range("C10").Select
End Sub