Help to insert a date using a macro in excel [Solved/Closed]

Report
-
 Matt -
Hello,

I have a spread sheet with four tabs. I have written a macro to copy the tabs and rename them but I need to be able change the date which updates automatically in each sheet to a static date showing the date the macro was run. At the moment it insert 6/10/2010 which is the date I created the macro.

Any help would be appreciated.

Nina

Here is my code:
Sub Baseline()
'
' Baseline Macro
' Macro recorded 10/06/2010 by
'

'
Sheets(Array("Project View", "Year View (2010)", "Year View (2011)", _
"Year View (2012)")).Select
Sheets("Year View (2012)").Activate
Sheets(Array("Project View", "Year View (2010)", "Year View (2011)", _
"Year View (2012)")).Copy Before:=Sheets(1)
Sheets("Project View (2)").Select
Sheets("Project View (2)").Name = "Project View (Baseline)"
Sheets("Year View (2013)").Select
Sheets("Year View (2013)").Name = "Year View (2010)Baseline"
Sheets("Year View (2014)").Select
Sheets("Year View (2014)").Name = "Year View (2011)Baseline"
Sheets("Year View (2015)").Select
Sheets("Year View (2015)").Name = "Year View (2012)Baseline"
ActiveCell.Select
ActiveSheet.Unprotect
ActiveCell.Offset(-15, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "6/10/2010"
Sheets("Year View (2011)Baseline").Select
ActiveSheet.Unprotect
ActiveCell.Offset(-15, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "6/10/2010"
Sheets("Year View (2010)Baseline").Select
ActiveSheet.Unprotect
ActiveCell.Offset(-15, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "6/10/2010"
Sheets(Array("Project View (Baseline)", "Year View (2010)Baseline", _
"Year View (2011)Baseline", "Year View (2012)Baseline")).Select
Sheets("Year View (2012)Baseline").Activate
Sheets(Array("Project View (Baseline)", "Year View (2010)Baseline", _
"Year View (2011)Baseline", "Year View (2012)Baseline")).Move
Windows("EDS_626Phoenix_Tracking spreadsheet.xls").Activate
ActiveCell.Offset(-4, 1).Range("A1").Select
End Sub

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
Change these lines
ActiveCell.FormulaR1C1 = "6/10/2010"

to

ActiveCell.FormulaR1C1 = Date
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

That fixes it many thanks
Thanks that helped me as well :)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!