Changing all remaining sheets to values, keeping the table format.

[MPaul100]

Hi, I have the following code, that should delete some sheets, change to values and protect the other ones, save and close. it works, except for one sheet(Units) that contains a table. In this one, the content gets twisted, showing formulas pasted as values and out of 500 lines, 25-30 are copied everywhere.
I have no clue why. Can someone take a look on it?

Sub SaveReport()
    Dim xWs As Worksheet
    Dim path As String
    Dim name As String
    Dim pwd As String


    path = ""
    name = "Balance" & ThisWorkbook.Sheets("Admin").Range("F3").Value
    pwd = "password1"
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual
    For Each xWs In Application.ThisWorkbook.Worksheets
        If xWs.name <> "Units" And xWs.name <> "Overview" And xWs.name <> "OM" And xWs.name <> "OD" Then
            xWs.Delete
        End If
    Next
    For Each xWs In ThisWorkbook.Worksheets
        With xWs.UsedRange
            .Value = .Value
        End With
        xWs.Protect Password:=pwd, UserInterfaceOnly:=True
    Next xWs
    
ThisWorkbook.SaveAs FileName:=path & name, FileFormat:=51
ThisWorkbook.Close savechanges:=False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub

 

[MPaul100]

Fixed by changing second For Each to:

    For Each xWs In ThisWorkbook.Worksheets
         For Each rng In xWs.UsedRange
            If rng.HasFormula Then
                rng.Formula = rng.Value
            End If
        Next rng
        xWs.Protect Password:=pwd, UserInterfaceOnly:=True
    Next xWs