Replaying a pivot table in a macro

[rockyire]

Hello

I recorded a simple pivot table using a macro however when i try to repay it i get the error:

'Unable to get the pivot tables probperty of the worksheet class'

The debugger points at this line of code:

I renamed the pivot table so its not clashing with an already existing table and other then that i dont know or cant find out what to do. Any advice would be greatly appreciated.

Thanks a million

[JR@SGC]

can you post a bit more of the surrounding code?
Or your workbook(in a compressed folder)?

[rockyire]

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'DAAREP Month'!R1C5:R329C5").CreatePivotTable TableDestination:= _
        "'[VIP Report.xls]Interim'!R1C1", TableName:="PivotTable6", DefaultVersion _
        :=xlPivotTableVersion10
    ActiveWorkbook.ShowPivotTableFieldList = True
        
        
         With ActiveSheet.PivotTables("PivotTable6").PivotFields("Category")
        .Orientation = xlRowField
        .Position = 1
        Range("A1").Select
    End With
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("Category"), "Count of Category", xlCount
    Sheets("Interim").Select

Thank you!!!!

[JR@SGC]

Just a two quick questions:
1.You renamed your pivot table from 'PivotTable6' or to 'PivotTable6'.
2. In the following

With ActiveSheet.PivotTables("PivotTable6").PivotFields("Category")
        .Orientation = xlRowField
        .Position = 1

what is "Catagory" (in the pivot fields part) is it a field you created like row or column?

[rockyire]

Hi JR

I changed it to PivotTable6 and category is the name of the column that i want to construct the pivot table from.

Thanks again