I have written the macro below to export data to separate files (a single table / single worksheet to one workbook) that works fine. I am attempting to add code to the worksheet to highlight updates made by users to the values in the exported worksheet. I can add the code to the workbook, as illustrated, where it does not work, but cannot tweak the macro to add the code to the worksheet, where it does work.
Please point out my coding error!

Sub CopyTablesToNewFiles_DeleteConnections_Worksheet_Change2()
Dim wbSource, wbNew As Workbook
Dim ws As Worksheet, wsNew As Worksheet
Dim savePath, currentDate, wsName, Filename As String

currentDate = Format(Date, "YYYYMMDD")
Set wbSource = ThisWorkbook

For Each ws In wbSource.Worksheets
    ws.Copy
    Set wbNew = ActiveWorkbook

        'This gives a Type MisMatch error
        'wbNew.VBProject.VBComponents.Item(wsNew).CodeModule.AddFromString ( _

        'Copies code to ThisWorkbook, need to copy to new worksheet wsNew
        wbNew.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.AddFromString ( _
            "Private Sub Worksheet_Change(ByVal Target As Range)" & vbCrLf _
            & "Target.Interior.ColorIndex = 27" & vbCrLf _
            & "End Sub")
        
    savePath = "C:pathTest"
    wbNew.SaveAs Filename:=savePath & currentDate & "_" & ws.Name & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
    wbNew.Close
Next ws

'wbSource.Close

End Sub