Error while creating pivot table in recording macro RRS feed

  • Question

  • im getting error " Run time error 1004: unable to get the pivottables property of the worksheet class

    below is the code

    Sub Macro16()
    ' Macro16 Macro
    ' Keyboard Shortcut: Ctrl+t
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Premium Data!R1C1:R25000C11", Version:=xlPivotTableVersion14). _
            CreatePivotTable TableDestination:="", TableName:= _
            "PivotTable14", DefaultVersion:=xlPivotTableVersion14
        Sheets("Premium Pivot").Select
        Cells(2, 2).Select
        With ActiveSheet.PivotTables("PivotTable14").PivotFields("RM")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveSheet.PivotTables("PivotTable14").AddDataField ActiveSheet.PivotTables( _
            "PivotTable14").PivotFields("KL"), "Sum of KL", xlSum
    End Sub

    Thursday, April 16, 2015 6:21 AM


  • You have TableDestination:="" which means you are not specifying a destination and therefore a new sheet gets created and it becomes the active sheet. You are selecting Sheets("Premium Pivot") which will not have the pivot table and the remainder of the code references the ActiveSheet.

    Either delete the line Sheets("Premium Pivot").Select or set the table destination like the following.

    TableDestination:="'Premium Pivot'!R3C1"      (Note the single quotes around the sheet name that contains a space. Edit the range on the destination to suit your requirements.)

    Regards, OssieMac

    • Marked as answer by L.HlModerator Tuesday, April 28, 2015 12:01 AM
    Friday, April 17, 2015 7:59 AM