VBA Pivot table variable

[CarolynS]

Hi

I have the below code to create a pivot table and have set the pivot table as the variable, PT:

Dim WB As Workbook
Dim WS As Worksheet
Dim WSsb As Worksheet
Dim FinalRow As Long
Dim FinalCol As Integer
Dim PRange As Range
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PF As PivotField
Set WB = ActiveWorkbook
'create pivot table to show status by brand (excluding those marked in franchise id field as exclusions)
    Set WS = Worksheets("Raw_Data")
    Set WSsb = Sheets.Add(after:=Sheets(Sheets.Count))
    WSsb.Name = "Status by Brand"
    
    ' Define input area and set up a Pivot Cache
    FinalRow = WS.Cells(Rows.Count, 1).End(xlUp).Row
    FinalCol = WS.Cells(1, Columns.Count). _
    End(xlToLeft).Column
    Set PRange = WS.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = WB.PivotCaches.Add(SourceType:= _
    xlDatabase, SourceData:=PRange)
    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSsb. _
    Cells(1, 1), TableName:="StatusByBrand")
    ' Turn off updating while building the table
    PT.ManualUpdate = True
    ' Set up the row, column & filterfields
    PT.AddFields RowFields:=Array("Brand", "BU"), ColumnFields:="Certification - Status", PageFields:="Person - Franchise ID"
    'set filter
    Set PF = WSsb.PivotTables("StatusByBrand").PivotFields("Person - Franchise ID")
    PF.EnableMultiplePageItems = True
    PF.PivotItems("Yes - 3rd Party").Visible = False
    PF.PivotItems("Yes - Excluded").Visible = False
    ' Set up the data fields
    With PT.PivotFields("Person - Username")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
    End With
    ' Calc the pivot table
    PT.ManualUpdate = False
    'PT.ManualUpdate = True
    
'add columns to status by brand table for calculations
    WSsb.PT.TableRange2.Copy
    WSsb.Range("A1").PasteSpecial (xlPasteValuesAndNumberFormats

PT is working ok to add fields to the table etc but when i try to set the report filter using Set PF = WSsb.PT.PivotFields("Person - Franchise ID") I'm geting an error (method or data member not found) and I've had to change PT to the full pivot table name (PivotTables("StatusByBrand")).

The same is happening at the end of the code for the line WSsb.PT.TableRange2.Copy

Can anyone tell me why this is happening and how i can rectify it without referring to the pivot table by name each time? I am using excel 2007

Thanks