Solved Updating date only when macro run

July 24, 2012 at 08:28:00
Specs: Windows XP
Here is the code i have (thanks!)

'Save Active sheet name
actName = ActiveSheet.Name
'Copy Active sheet to end of workbook
Sheets(actName).Copy After:=Sheets(Sheets.Count)
'Get name for sheet from user
archiveName = Application.InputBox _
("Please enter week submitting")
'Rename last sheet
Sheets(Sheets.Count).Name = archiveName
'Protect sheet
Sheets(Sheets.Count).Protect Password:="Secret"
'Clear range in original sheet
With Sheets(actName)
End With
End Sub

Now i want to be able to place a command in the code to update the date only when the macro is submitted. i tried using a column in excel with the date, and having a formula automatically update it: =vlookup(today(),range), but the probelm with that is that if the sheet is not submitted yet the dates update anyway. so then when the sheet is submitted it has the wrong dates. i have a row for monday, date; row for tuesday, date; etc. every week once the macro is run and clears out the active sheet, i want the new weeks dates updated. any suggestions? i have looked through posts but nothing really relevant to mine, or i just dont understand it well.

See More: Updating date only when macro run

Report •

July 24, 2012 at 09:03:37
✔ Best Answer
A posting tip:

Before posting any more code or data in this forum, please click on the blue line at the end of this post and read the instructions on how to post code in this forum. Thanks.

This code will insert a "hard coded" copy of the system date in Sheet(1)!A1 each time the maco is run. Since it is hard coded, it will not be volatile like the TODAY() function is.

Sheets(1).Range("A1") = Date

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question