Create Pivot Table in VBA Error 1004 Unable to get the Pivot Tables Property

[mheilberg]

Excel 2010
Windows 2007
I have checked all other posts with the same issue and cannot find and example that matches mine.

I am creating a pivot table in VBA. I am getting the error message:
Runtime error 1004-Unable to get the Pivot Tables property
I have checked that the pivot table name is correct and it exists in the current focus\named worksheet.

I would appreciate it if anyone has the time to take a look at my code, and let me know if the problem is evident. Thanks for your help!

The error pops up when I begin assigning fields to the pivot table (labeled below)

Sub pivot()
Dim strSheetName As String     'Source Data Sheet
Dim strCurrentRegion As String  'Source Data Range
Dim strSourceData As String     'Source Sheet &  "!" & Source Range

Dim PTCache As PivotCache
Dim PT As PivotTable
Dim strPivotTableName As String 'Pivot Table Dynamic Name
    
    
    
    strPivotTableName = "PT " & Replace(Now(), "/", "_")
    strPivotTableName = Replace(strPivotTableName, ":", "-")
    lExcelVersion = Application.Version
    strSheetName = ActiveSheet.Name
    
   
    Selection.CurrentRegion.Select
    
    strCurrentRegion = Selection.Address
    strSourceData = strSheetName & "!" & strCurrentRegion
    
    Set PTCache = ActiveWorkbook.PivotCaches.Add _
        (SourceType:=xlDatabase, _
        SourceData:=strSourceData)
        
    Set PT = PTCache.CreatePivotTable _
        (TableDestination:="", _
        TableName:=strPivotTableName)


'========================================================================================
'At the following WITH clause, Runtime error 1004-Unable to get the Pivot Tables property
'When I comment out the WITH clause, the error pops up for the next "ActiveSheet.PivotTables(PT) clause.
'========================================================================================

    With ActiveSheet.PivotTables(PT).PivotFields( _
        "Assigned To")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables(PT).AddDataField ActiveSheet. _
        PivotTables(PT).PivotFields("Opened On"), _
        "Sum of Opened On", xlSum
    ActiveSheet.PivotTables(PT).AddDataField ActiveSheet. _
        PivotTables(PT).PivotFields("Opened in Period"), _
        "Sum of Opened in Period", xlSum
    With ActiveSheet.PivotTables(PT).DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables(PT).AddDataField ActiveSheet. _
        PivotTables(PT).PivotFields("Closed in Period"), _
        "Sum of Closed in Period", xlSum
    ActiveSheet.PivotTables(PT).AddDataField ActiveSheet. _
        PivotTables(PT).PivotFields("Left Open On"), _
        "Sum of Left Open On", xlSum
    ActiveSheet.PivotTables(PT).AddDataField ActiveSheet. _
        PivotTables(PT).PivotFields("Total Opened in Period"), _
        "Sum of Total Opened in Period", xlSum
    ActiveSheet.PivotTables(PT).PivotFields( _
        "Sum of Opened On").Caption = "Opened at Start"
    ActiveSheet.PivotTables(PT).PivotFields( _
        "Sum of Opened in Period").Caption = "Opened during Period"
    ActiveSheet.PivotTables(PT).PivotFields( _
        "Sum of Closed in Period").Caption = "Closed during Period"
    ActiveSheet.PivotTables(PT).PivotFields( _
        "Sum of Left Open On").Caption = "Open at end of Period"
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

PT is an object variable so in place of:

With ActiveSheet.PivotTables(PT)

you can use:

With PT

 

[mheilberg]

Thank you very much for your fast response. I made the change and it worked for the first clause.
If I may trouble you further, now that i make it past the Assigned To field, I error out on the next clause

  ActiveSheet.PivotTables(PT).AddDataField ActiveSheet. _
        PivotTables(PT).PivotFields("Opened On"), _
        "Sum of Opened On", xlSum

Before bothering you, I did try rewriting the code following your earlier example, with no luck.
Any advice?

 

[Andrew Poulsom]

Did you replace all occurrences of ActiveSheet.PivotTables(PT) with PT?

 

[mheilberg]

Did you replace all occurrences of ActiveSheet.PivotTables(PT) with PT?

Yes I did. I received the following error
Error 438 Object doesn't support this property or method

original as recorded by Macro

    ActiveSheet.PivotTables(PT).AddDataField ActiveSheet. _
        PivotTables(PT).PivotFields("Opened On"), _
        "Sum of Opened On", xlSum

My changes

    PT.AddDataField ActiveSheet. _
        PT.PivotFields("Opened On"), _
        "Sum of Opened On", xlSum

I have written vba for creating Pivot Tables, but not like this table (Multiple aggregates in the datafield, no data columns) Obviously, I have discovered my ignorance regarding this particular syntax. Thank you for your continued advice.
Do you know of a comprehensive resource regarding PivotTables\vba?

 

[Andrew Poulsom]

You don't need ActiveSheet.. Remember that PT is an object variable. It's not a string and it doesn't need to be qualified.