Pivot Table Run Time Error

[ryan8200]

I tried to create pivot table on existing sheets but hitting run time error 13 (type mismatch) . I have changed data and pivot range to reference by address but still not workable. Please refer to my code as below:

[/

     Dim DSheet As Worksheet
     Dim PCache As PivotCache
     Dim PTable As PivotTable
     Dim Drange As Range
     Dim Prange As Range
     Dim LastRow As Long
     Dim LastCol As Long

    'Define Data Range
     Set DSheet = ThisWorkbook.Worksheets("My")
     LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
     LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
     Set Drange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

    'Define Pivot Cache
    Set Prange = DSheet.Cells(1, 13)
    Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:="'" & DSheet.Name & "'!" & Drange.Address(ReferenceStyle:=xlR1C1)). _
    CreatePivotTable(TableDestination:="'" & DSheet.Name & "'!" & Prange.Address(ReferenceStyle:=xlR1C1), _
    TableName:="MyPivotTable")
]

 

[ryan8200]

The above error is due to last 3 lines ?

 

[Alex Blakenburg]

Change your ('Define Pivot Cache) code to this:-

    'Define Pivot Cache
    Set Prange = DSheet.Cells(1, 13)
    Set PTable = ActiveWorkbook.PivotCaches.Create _
        (SourceType:=xlDatabase, SourceData:=Drange). _
        CreatePivotTable(TableDestination:=Prange, _
        TableName:="MyPivotTable")

 

[RoryA]

You should really use ThisWorkbook, not ActiveWorkbook, since that's where the source data is.