Macro to Save Current File with Values at the end

[howard]

I have a macro to copy and paste all the data as values

I would like a macro to save the file to contains values just before .xls

eg BR1 Stats - Dec 2020.xls

need this to save as BR1 Stats - Dec 2020 Values.xls using a macro

It would be appreciated if someone can assist me

 

[LazyBug]

If I've get your goal correctly:

Sub h()
Dim wb As Workbook, OldName$, NewName$
Set wb = ActiveWorkbook
    OldName = wb.Name
    NewName = Left(OldName, InStrRev(OldName, ".") - 1)
    NewName = NewName & " Values.xls"

    wb.SaveAs wb.path & "\" & NewName, wb.FileFormat
End Sub

 

[howard]

Many thanks for the help. Code works perfectly

 

[howard]

I have another file which it an .xlsm file and want a macro to save it as a .xlsx file

i tried to amend your code,but cannot get it to work

 Sub saveas_XLSX()
ActiveWorkbook.Save

Dim wb As Workbook, OldName$, NewName$
Set wb = ActiveWorkbook

    OldName = wb.Name
NewName = Left(OldName, InStrRev(OldName, ".xlsm") - 1)
    NewName = NewName & " .xlsx"

    wb.saveas wb.Path & "\" & NewName, wb.FileFormat
    End Sub

I get error message below

kindly amend my code

 

[LazyBug]

Try replace

wb.SaveAs wb.Path & "\" & NewName, wb.FileFormat

with

wb.SaveAs wb.Path & "\" & NewName, 51

 

[howard]

Many Thanks for the amended code-It works perfectly