VBA to create pivot table - type mismatch error

[bookworm121]

I'm using the following code:

    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long
    Dim FinalCol As Long
    Set WSD = Worksheets("Sheet1")
    Set WSD2 = Worksheets("summary")
    For Each PT In WSD2.PivotTables
        PT.TableRange2.Clear
    Next PT
    FinalRow = WSD2.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD2.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    Set PRange = WSD2.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:=PRange)
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), _
    TableName:="PivotTable1")

What I want to do is to use the table from worksheet "summary", then use it to create a pivot table in "sheet1". I also want the pivot table to work on a range of data that can be changing... And I need to do with without using the offset tool (it has to all be VBA code)

Unfortunately I keep getting a type mismatch error at the line

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:=PRange)

Any help would be appreciated

 

[Andrew Poulsom]

Try:

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:="'" & PRange.Parent.Name & "'!" & PRange.Address(True, True, xlR1C1))

You should als be able to set PRange like this:

Set PRange = WSD2.Cells(1, 1).CurrentRegion