Marco to insert date

Posted by Paul on October 26, 2001 8:41 AM

I need a Marco that will insert a date: If cell G27 on sheet 1 has anything in it I need to put todays date in sheet 1 cell S2 & in sheet 2 cell S2. I can't just use =today() because I don't want the date to change after it has been put in. Thanks for your help.

Posted by Barrie Davidson on October 26, 2001 9:39 AM

Try this

Sub Insert_date()
' Written by Barrie Davidson
If Sheets("sheet1").Range("G27") &LT;&GT; "" Then
Sheets("sheet1").Range("s2").Value = Now
Sheets("sheet2").Range("s2").Value = Now
End If
End Sub

Regards,
BarrieBarrie Davidson

Posted by Paul on October 26, 2001 10:29 AM

Barrie I could not get this to work, I tried it in a module and in the sheet code, where does it need to go. I forgot to say I am using excel '97. I must not be doing something right

Posted by Barrie Davidson on October 26, 2001 10:47 AM

The code should be put in a module. This worked fine for me when I tested it. Do you have sheets named "Sheet1" and "Sheet2" in your workbook or are they named "Sheet 1" and "Sheet 2"?

Barrie

I could not get this to work, I tried it in a module and in the sheet code, where does it need to go. I forgot to say I am using excel '97. I must not be doing something right

Barrie Davidson

Posted by Paul on October 26, 2001 11:27 AM

Sorry, your Marco works fine, when I ran it, but I need it to automatically put the date in when something is put in put in G27 I could not get this to work, I tried it in a module and in the sheet code, where does it need to go. I forgot to say I am using excel '97. I must not be doing something right

Posted by Barrie Davidson on October 26, 2001 11:35 AM

Okay, put this code in the worksheet's code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
' Written by Barrie Davidson
If Target.Address = "$G$27" And Target.Value &LT;&GT; "" Then
Sheets("sheet1").Range("s2").Value = Now
Sheets("sheet2").Range("s2").Value = Now
End If
End Sub

Regards,
Barrie

Sorry, your Marco works fine, when I ran it, but I need it to automatically put the date in when something is put in put in G27 : The code should be put in a module. This worked fine for me when I tested it. Do you have sheets named "Sheet1" and "Sheet2" in your workbook or are they named "Sheet 1" and "Sheet 2"? : Barrie : I could not get this to work, I tried it in a module and in the sheet code, where does it need to go. I forgot to say I am using excel '97. I must not be doing something right

Barrie Davidson

Posted by Paul on October 26, 2001 12:00 PM

That works! Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)