Menu
Ask a question Report

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

- Jun 10, 2010 at 10:54 PM - Latest reply:  Matt - May 22, 2015 at 05:31 AM
Excel Ask a question Report
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
See more 

1 reply

Best answer
Report Respond to rizvisa1
Posts
4481
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 Jun 11, 2010 at 04:46 AM
1
Thank you
Change these lines
ActiveCell.FormulaR1C1 = "6/10/2010"

to

ActiveCell.FormulaR1C1 = Date

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1699 users this month

Report
- Jun 14, 2010 at 03:37 PM
That fixes it many thanks
Report
- May 22, 2015 at 05:31 AM
Thanks that helped me as well :)
Ask a question