paste value of cell when saving as a new file

winstela

New Member
Joined
Feb 24, 2019
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,685
Messages
6,120,876
Members
448,993
Latest member
InquisitiveFrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top