Consulting

Results 1 to 3 of 3

Thread: Pivot Table error - Tabular format

  1. #1

    Pivot Table error - Tabular format

    I'm trying to run a macro which will create a pivot table for me so that it ends up looking like this:

    Header1 Header2 Sum of Units
    text text 124
    text text 8689
    text text 36778

    However, I'm getting the following error: 'unable to get the pivottables property of the worksheet class.' Does anyone know how to fix this?? Not sure what I'm doing wrong, very unfamiliar with pivot tables, especially when used in vba. Below is my current code:

        Dim pg As PivotCache
        Dim pj As PivotTable
    
    With Sheets("Sheet1")
        Set pg = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:="'" & .Name & "'!" & .Range("A1").CurrentRegion.Resize(, 3).Address(ReferenceStyle:=xlR1C1), _
        Version:=xlPivotTableVersion14)
    End With
    Set pj = pg.CreatePivotTable(TableDestination:="", DefaultVersion:=xlPivotTableVersion14)
    ActiveWorkbook.ShowPivotTableFieldList = True
    With pj
        With .PivotFields("Header1")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Header2")
            .Orientation = xlRowField
            .Position = 2
        End With
        .AddDataField .PivotFields("Units"), "Sum of Units", xlSum
    End With
        ActiveSheet.PivotTables("PivotTable3").RowAxisLayout xlTabularRow
        ActiveSheet.PivotTables("PivotTable3").PivotFields("SEDOL").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable3").PivotFields("NAME").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        ActiveSheet.PivotTables("PivotTable3").PivotFields("UNITS").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveWorkbook.ShowPivotTableFieldList = False

    The row that is highlighted with the error is: ActiveSheet.PivotTables("PivotTable3").RowAxisLayout xlTabularRow

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,598
    Location
    You should be using pj and not ActiveSheet.PivotTables("PivotTable3")
    Be as you wish to seem

  3. #3
    Oh dang it, thank you!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •