Error 1004: invalid references when creating a pivot table with vba

Question

hi, i've in my excel file a sheet with 3 columns (id,type,month).

knowing that many items appearing more than once, i need to get the number of occurances of each id per month then to get the number of ids appearing twice, appearing three times, ...

i was told that pivot table is the solution. so i created a pivot table with macro recorder. when i try to execute the macro it send me the error 1004: invalid references.

here my macro:

Sub Relivr()
'
Dim LastRow As Long

LastRow = ActiveWorkbook.Worksheets("Delivery").Range("A65536").End(xlUp).Row

'the error comes from this line
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'Delivery'!R1C1:R" & LastRow & "C4", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="'Delivery'!R1C13", TableName:="Tableau croisé dynamique2", DefaultVersion:=xlPivotTableVersion12*
    Sheets("Delivery").Select
    Cells(1, 13).Select
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields("ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotFields( _
        "Month")
        .Orientation = xlColumnField
        .Position = 1
    End With

    ActiveSheet.PivotTables("Tableau croisé dynamique2").AddDataField ActiveSheet. _
        PivotTables("Tableau croisé dynamique2").PivotFields("type"), _
        "Nb delivries", xlCount

    ActiveSheet.PivotTables("Tableau croisé dynamique2").RowGrand = False

    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(RC[6]:R[12342]C[6],""=2"")"
    Range("H4").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(R[-1]C[7]:R[12341]C[7],""=2"")"

End Sub

i'm using office 2007

Any and all help is appreciated.

Thanks

Answer 1

Two ways you could do this:

1. As you say, a pivot table would be the obvious choice, but I'm not sure why you think that VBA would preclude you using one? Manipulating pivot tables in VBA is relatively easy once you get the hang of it - the macro recorder is really useful for learning the ins and outs of the object model, or here is a starter tutorial.

2. Alternatively, you could connect to the data using ADO and use a SQL query to obtain the required information. Note that this would require the data to be suitably formatted on a separate worksheet, but this could be managed as part of the program. Microsoft have a good guide to using ADO to query Excel data.