paste value of cell when saving as a new file

[winstela]

I have an excel template that I use daily to paste data from other files
On the summary sheet I use formula =Today() in cell C3
When I do a file save as to save the changes to a new workbook .xlsx I want the date in C3 not the the formula, in the new workbook.
Would I use before save with the active workbook?
I want the template to still have the formula
Thanks
winstela

 

[paldob]

Could you not create a routine to hard key the date and then save copy as "workbooknamexxxx.xlsx" - maybe an input box for you to enter the name, with the path and file type within the routine something like

Dim Path As String, wbName As String, Ext As String
  Path = ThisWorkbook.Path
  wbName = Application.Inputbox ("Name of new File")
  Ext = ".xlsx"

  wbName = Path & wbName & Ext

  Sheets("sheetname").Range("C3") = Format(NOW,"dd/mm/yyyy")

 ThisWorkbook.SaveCopyAs wbName
  
  Sheets("sheetname").Range("C3").Formula = "=Today()"

  ThisWorkbook.Save

Then re-enter the code into your template and do .save on your template?

 

[winstela]

Thanks paldob,

I could not get the code to work correctly, I put it in This workbook before save event on the template but it asks me to rename the file twice. Not sure if this is were the code should go.

I managed to get a solution by using This workbook open event with

Private Sub Workbook_Open()


Worksheets("Summary").Range("c3").Formula = "=TODAY()"
 With Selection.Copy
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
End With


End Sub