VBA: Change Source Data of Pivot

[jamesuk]

Hi All

So after much searching through many posts about how to change the source data of a pivot table, I finally found some code that seems to work for me, but for some reason I keep getting the following error:

Run-time error '-2147024809 (80070057)':


Cannot open PivotTable source file
'\\xxxxx\me\xxxx'

I think it may have something to do with the "DataArea" part, but I cannot figure out why!

the data starts in the tab, "1_SOA_-_With_Chat" ,from cell A2 to O9999

Sub Macro1()


Sheets("1_SOA_-_With_Chat").Activate
Range("A2").Select
Selection.Range("A1:O9999").Select


DataArea = "SOA Rec!R1C1:R" & Selection.Rows.Count & "C" & Selection.Columns.Count
 
    Sheets("Pivs").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataArea, _
        Version:=xlPivotTableVersion14)


End Sub

Can anyone see what I'm doing wrong?

My thanks for your help in advance!

 

[pbornemeier]

I am not sure why your code is not working

This is what I use to reset PT Source Ranges

Sub ResetPTSourceRange(sWorksheeetName As String)
    'Assumes the source data for a PT starts at Range("A1")
    'Resets the source data to occupy the current region for Range("A1") on the current source data worksheet
    'Added Replace single quotes to 3rd line 20160712
    Dim pt As PivotTable
    
    Set pt = Worksheets(sWorksheeetName).PivotTables(1)
    Debug.Print "Before PT Source: " & pt.SourceData
    pt.ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Split(pt.SourceData, "!")(0) & "!" & _
        Worksheets(Replace(Split(pt.SourceData, "!")(0), "'", "")).Range("A1").CurrentRegion.Address(, , xlR1C1), _
        Version:=xlPivotTableVersion14)
    Debug.Print " After PT Source: " & pt.SourceData
    Set pt = Nothing

End Sub