paste value of cell when saving as a new file

winstela

New Member
Joined
Feb 24, 2019
Messages
6
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

paldob

New Member
Joined
Apr 23, 2018
Messages
28
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

Code:
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?
 
Last edited:

winstela

New Member
Joined
Feb 24, 2019
Messages
6
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,665
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top