Help fix macro

Posted by Ken on December 21, 2001 7:59 AM

Hi all,

I am just learning VBA and want to copy the value in cell B1 to cell C1 if the value in cell A1 equals todays date. Here is the macro I tried to write.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Sheet1").Range("A1").Value = Now Then
Range("B1").Copy
Range("C1").PasteSpecial (xlPasteAll)
Else: Exit Sub
End If
End Sub

Thank you
Ken

Posted by Mark O'Brien on December 21, 2001 8:08 AM

Ken

You've used the "Now" function, this returns the date and time. You should just use "Date". Also, you only want this macro to run if the value hasn't already been copied into C1, so you want to add an extra condition to your macro, try this one:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Sheet1").Range("A1").Value = Date _
And Sheets("Sheet1").Range("C1").Value = "" Then
Range("B1").Copy
Range("C1").PasteSpecial (xlPasteAll)
End If
End Sub

Posted by Ken on December 21, 2001 8:58 AM

Thanks Mark, works great!