Update Pivot Table Source Data With Dynamic Range Of Non Empty Cells

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 :)

Try this

Code:

---

   
 strMsg = NewPTData.Resize(, NewPTData.Columns.Count + 10).Address(, , xlR1C1, True)

    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strMsg _
        , Version:=xlPivotTableVersion12)

---

You're a wonderful wonderful person :) thanks so much, works at long last.
full code,

Code:

---

With ShipmentTimelinessWeekPrevious.Sheets("Data").Select
Dim NewPTData As Range
Dim strMsg As String
Set NewPTData = Range(Range("A4"), Range("A4").End(xlDown))
    End With
   
With ActiveWorkbook.Worksheets("Shipment Timeliness Report").Select
strMsg = NewPTData.Resize(, NewPTData.Columns.Count + 10).Address(, , xlR1C1, True)

    ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strMsg _
        , Version:=xlPivotTableVersion12)
End With

---