I am trying to implement track changes in exported Excel files. I have a master file that uses Power Query to create data tables from a master source. The data tables need to be distributed so that users can edit the content locally, not in the data source. The data export to separate files (one table to one workbook) works fine, but I get a run-time error 1004 for HighlightChangesOptions when the macro tries to turn on track changes. I read that Track Changes is a legacy functionality, but that it should be possible to activate nonetheless.

    Sub CopyTablesToNewFiles_DeleteConnections_TrackChanges()
    Dim wbSource, wbNew As Workbook
    Dim ws, 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
        
            'Remove connections to original query
            Do While wbNew.Connections.Count > 0
            wbNew.Connections.Item(ActiveWorkbook.Connections.Count).Delete
            Loop
            
            'Convert table to range to support track changes (only 1 table in sheet)
            Set wsNew = ActiveSheet
            For Each objList In wsNew.ListObjects
                objList.Unlist
            Next objList
            
            'Turn on track changes
            'Gives Run-time error 1004 Method HighlightChangesOptions of object _workbook failed
            With wbNew
            .HighlightChangesOptions When:=xlAllChanges
                .ListChangesOnNewSheet = False
                .HighlightChangesOnScreen = True
            End With
            
        savePath = "C:PathTest"
        wbNew.SaveAs Filename:=savePath & currentDate & "_" & ws.Name & ".xlsx"
        wbNew.Close
    Next ws

    'wbSource.Close
End Sub