VBA Save sheet with new name and change formula to values in selected cells

[CCoetzee]

Morning

i'm using the below to save my spreadsheet with a new name and location.

Sub SaveInWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
ActiveSheet.Shapes("Rounded Rectangle 1").Delete
NewFN = "C:\Users\Fonnie\Dropbox\Ck007 Mechanical\Invoices\" & " " & Range("G2").Value & " " & Range("B11").Value & " " & Range("E11").Value & " " & Range("E12").Value & " " & Range("E2").Value & ".xlsm"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close

End Sub


But what i need is when i save my worksheet the formulas in cells (B20:G60) must be saved as values.

can you please assist?

Regards Christelle

 

[Anthony47]

Try adding this line in this position:

ActiveSheet.Copy
Range("B20:G60").Value = Range("B20:G60").Value         '<<< ADD HERE
   ActiveSheet.Shapes("Rounded Rectangle 1").Delete

Bye

 

[CCoetzee]

Thank you

If i add the code it removes the formulas from my "master" sheet and saves the copy with the formulas?

The copy must be saved without formulas and my "master" sheet must keep the formulas.

Any idea why this is happening?

Regards Christelle

 

[Anthony47]

Any idea why this is happening?

WHAT is happening?

 

[Anthony47]

And also, which "vba module" contains your Sub SaveInWithNewName? It is in the header of the vba window, after "Microsoft Visual Basic Application Edition, ...."
Bye

 

[Marc L]

Hi,​

​

first obviously use SaveCopyAs rather than SaveAs ! Then open the new workbook and replace formulas by values and save the workbook …​

 

[Anthony47]

I guess that CCoetzee has no problem in saving a copy of the worksheet (the sheet has just been copied in a new workbook using the command ActiveSheet.Copy), but he needs to save values and not formulas.
My current "guess" is that he tried to say that my added line of code leave the copied sheet with the formulas, whereas it remove the formulas from the master sheet (he terminated the phrase by a question mark, but probably was willing to use the exclamation mark).
And my final "best guess" is that he inserted the code into a vba class module, rather then a vba standard module; if this is the case then my Range("B20:G60") will refer to the object the class module refers to and not the activesheet.
If these guesses are correct then my preferred suggestion is to move the code to a vba "standard module" (the ones you create by using the command Menu /Insert /Module), or to use

ActiveSheet.Range("B20:G60").Value = ActiveSheet.Range("B20:G60").Value

I write these information because it seems that we and the OP have non-overlapping timezones, so that a full day is needed before we can get an answer to the questions posed.

Bye

 

[Marc L]

​

Yes you are right, in particular if the original worbook has several worksheets. But for only one another way is SaveCopyAs …​