Hi All,

I was wondering if someone could help me with a macro I have been trying to create. I am fairly new at macros so please bare with me. I need to a macro to create a pivot table that will pivot Deal No Against Base Amount. However after I create the macro and I try to rerun it, and error always occur. If anyone could help me that would be great.

The code is below and the bold part seems to be causing the error. Again the code should be adaptable. The columns will always be the same, but the rows can vary. Any help on this issue would be appreciated.

Thanks

    

Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R7C11", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="Sheet4!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Deal No")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Base Settle Amount"), "Sum of Base Settle Amount", _
        xlSum
End Sub