paste value of cell when saving as a new file

winstela

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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

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
23
Office Version
  1. 2016
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,987
Members
430,100
Latest member
namhnz

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
Top