Pivot graph data when creating a new tab from a template.

[Nippyzap]

Hi I have been trying to figure this out for a few days but cant seem to get what I need to work. I have a master list "SB check data" which contains a table with 4 columns serial number, survey date, measurement and comments with the first survey data for 45 pieces of equipment. I also have a template sheet that contains a pivot graph and input table to record the survey date, measurement and comments for a single serial number. I have the below code to create a new tab based on the template name for each serial number and to copy the survey date, measurement and comments for that serial number into the first row of the table in the new tab. This is where I run into issues, I would then like it to refresh the pivot table to reflect the data now in the table on the same tab but whatever I do it always seems to be linked back to the original template table. I have tryed in the code to make sure each table had a unique name and does the graph, I have also tried to make sure each graph has separate cache but my knowledge on this level of VBA/excel is very limited and I now think I am going in circles and missing something. can anyone point me in the direction of my errors. many thanks Martyn

Sub CreateAndPopulateTabs()
    Dim ws As Worksheet
    Dim templateSheet As Worksheet
    Dim newSheet As Worksheet
    Dim nameList As Range
    Dim cell As Range
    Dim name As String
    Dim lastRow As Long
    Dim chartObj As ChartObject
    Dim chartNumber As Long ' Counter for chart numbers
    Dim pt As pivotTable
    Dim newCache As PivotCache

    ' Set references to the relevant sheets
    Set ws = ThisWorkbook.Sheets("SB check data")
    Set templateSheet = ThisWorkbook.Sheets("Template")

    ' Find the last row in column A of "SB check data"
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Set the range of names to create sheets for
    Set nameList = ws.Range("A2:A" & lastRow)
    
    ' Initialize the chart number counter
    chartNumber = 1
    
    ' Loop through each name in the list
    For Each cell In nameList
        name = cell.Value

        ' Check if a sheet with this name already exists
        On Error Resume Next
        Set newSheet = ThisWorkbook.Sheets(name)
        On Error GoTo 0

        If newSheet Is Nothing Then
            ' Create a new sheet based on the template
            templateSheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            Set newSheet = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            newSheet.name = name
        End If
        
        ' Clear value in D19
        newSheet.Range("D19").ClearContents
        
        ' Copy data from columns B, C, and D to B19, C19, and E19
        newSheet.Range("B19").Value = ws.Cells(cell.Row, 2).Value ' Copy column B to B19
        newSheet.Range("C19").Value = ws.Cells(cell.Row, 3).Value ' Copy column C to C19
        newSheet.Range("E19").Value = ws.Cells(cell.Row, 4).Value ' Copy column D to E19

        ' Rename PivotCharts with a unique number
        For Each chartObj In newSheet.ChartObjects
            If Not chartObj.Chart.PivotLayout Is Nothing Then
                chartObj.name = "Chart" & chartNumber
                chartNumber = chartNumber + 1 ' Increment chart number
            End If
        Next chartObj

        ' Create a new PivotCache for the PivotTable in the chart and update the data source
        For Each pt In newSheet.PivotTables
            Set newCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, sourcedata:=newSheet.ListObjects(1).Range.Address)
            pt.ChangePivotCache newCache
        Next pt

        ' Refresh all PivotTables on the new sheet to ensure they use the new cache
        For Each pt In newSheet.PivotTables
            pt.RefreshTable
        Next pt

        ' Clear the reference for the next iteration
        Set newSheet = Nothing
    Next cell

    MsgBox "Tabs created, populated, and PivotCharts linked to local tables successfully!"
End Sub

I have