Hi
The code in this I found didn't work correctly and I had to change it. The code I used for this is below and works well so should work for you. Let me know if everything worked for you.
Inputs - Handle, Workbook, Worksheet, Range, PivotTableName, DataWorksheet
================================================================
Dim wb, ws, wd As Object
Dim excel, sheet As Object
Dim rng As String
wb = GetWorkbook(Handle, Workbook)
wd = wb.Worksheets(DataWorksheet)
ws = GetWorksheet(Handle, Workbook, Worksheet)
wb.Activate()
ws.Activate()
excel = wb.Application
sheet = excel.ActiveSheet
rng = sheet.Range(Range).Address(ReferenceStyle:=-4150)
rng = wd.Name & "!" & rng
ws.Activate()
sheet = excel.ActiveSheet
sheet.PivotTables(PivotTableName).SourceData = rng
sheet.PivotTables(PivotTableName).RefreshTable
