convert cells in worksheet to values

[Ray707]

You did it right. I meesed up

I don't know why, but it seems one can't use the Sheet Object With PasteSpecial. Anyway, this tested version uses the Worksheet object. I also corrected my brain fart with PasteSpecial Operation:=

    Dim wb1 As Workbook, wb2 As Workbook
    Dim sfilepath As String, sfilename As String
    Dim iformat As Integer
    Dim SINW As Long
    
    SINW = Application.SheetsInNewWorkbook 'Current default
      
    Application.SheetsInNewWorkbook = 1 'Changes current default
       Set wb1 = Application.ActiveWorkbook '<--- Original line of code
          wb1.ActiveSheet.UsedRange.Copy
        Set wb2 = Workbooks.Add
         wb2.Sheets(1).Cells(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats '<--Note changes in red
    Application.SheetsInNewWorkbook = SINW 'Resets current default

When stepping thru the code using F8, the first error was "PasteSPecial failed..." My brain fart. The next error was "Object doesn't support...," so I knew the error was either wb2 or Sheet1, the only two Objects in that line

Thank you, that worked. The only problem is I lose the formatting in the sheet so for example the colour coding in the tables and the bordering of the cells are all lost, and I would like to keep this. But in terms of hard coding the values it does exactly that so thanks!