Hello,

Please please please could someone smarter than me have a look at this. I have been working on this all day and feel like crying. I have two sheets on the same workbook. One ("Data") holds the info for the Pivot table source data. The other tab ("Shipment Timeliness Report") has the pivot table on.

I have written the following code to try and set the data source range from one sheet (down to the last cell with data in), then update the pivot table with this range on the second sheet.

Code:
With ShipmentTimelinessWeekPrevious.Sheets("Data").Select
Dim NewPTData As Range
Set NewPTData = Range(Range("A4"), Range("A4").End(xlDown))
    NewPTData.Resize(, NewPTData.Columns.Count + 10).Select
    End With
    
With ActiveWorkbook.Worksheets("Shipment Timeliness Report").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Sheets("Data").Selection _
        , Version:=xlPivotTableVersion10)
        Sheets("Shipment Timeliness Report").PivotTables.Refresh
End With
Anyone who can offer any help would be loved by me for ever