Thread: Pivot Table Using Macros

I am trying to insert a pivot table using Excel Macros. I thought that I could just record the creation of a pivot table, but when I try to run the macro a "Run-time error '1004'" appears. Does anyone know why? The code I tried to put in that does not work is:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "DataForCalculations!R2C1:R65536C54").CreatePivotTable TableDestination:= _
        "'[AOT Sample_NEW_v3.xls]Pivot'!R1C1", TableName:="PivotTable2", _
        DefaultVersion:=xlPivotTableVersion10

Any idea why this is not going through?

Thanks a ton!

Yoav

Sir,
Try this

Sub AddPivot(ByRef PivotSheet As Worksheet, ByRef PivotDestination As Range)
Dim ws As Worksheet, pt_rng As Range, pt As PivotTable

Set ws = PivotSheet
Set pt_rng = Range(ws.[A1], ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(0, Application.CountA(ws.Rows(1)) - 1))

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pt_rng).CreatePivotTable _
            TableDestination:=PivotDestination

Set pt = ws.PivotTables(1)

pt.AddFields RowFields:="Weeks Open"
pt.PivotFields("Closure Date").Orientation = xlDataField
PivotDestination.Offset(1).Group Start:=True, End:=True, By:=1

End Sub