Pivot table creation error 1004: Pivottable field name is not valid

[vinu315]

Hi all,

I am having a peculiar error with excel VBA today. In the code below, are two ways I am using to create a Pivot table. Option 1 is creating the pivot table in the same sheet that the data is in. Option 2 is creating the pivot table in a new sheet. Option 2 does not work. As you can see both codes refer to the exact same data set. All header columns are populated. Not sure where the error is coming from other than the location of the pivot table could be throwing a bug?

The error is as follows:
Run-time error '1004': The [ivottable field name is not valid. To create a Pivottable report, you m ust use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field.

I am using Excel 2010

Please help.

With ThisWorkbook
    Set wkSheet = .Worksheets.Add
   
    'This code works fine (Option 1)
    '.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "RM data!R1C1:R3C3", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:=Sheet1.Cells(1, 10), TableName:="PT3", DefaultVersion _
    :=xlPivotTableVersion14

    'This code results in an error (Option 2)
    .PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "RM data!R1C1:R3C3", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:=wkSheet.Cells(3, 1), TableName:="PT3", DefaultVersion _
    :=xlPivotTableVersion14
End With