Run time error '1004'

[albatross32]

I am getting this error message.

This is my code;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$9" Then

If Len(Target.Text) > 0 Then
Application.Wait (Now + TimeValue("0:00:01"))
Range("O9").Activate
Selection.ClearContents
Sheets("Sheet2").Activate
Range("P3").Activate
Range("P3").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False

End If

End If


End Sub

Any thoughts. Spoon feed me please. I am Excel competent but not VBA professional!

 

[Hermanito]

What are you trying to achieve, because that code does not make any sense to me: you clear a cell the user just changed, then you copy another cell to itself twice???

 

[albatross32]

I am using a 'heath robinson' method to reset a series of functions on Sheet2.

 

[Hermanito]

that means even less to me... please elaborate in detail...

 

[albatross32]

I have a fairly detailed set of functions on Sheet2 that trigger an event on Sheet1. My code is then designed to reset Sheet1. For some reason, when my Sheet2 trigger has been completed I have to perform a task in Sheet2 to correctly reset the functions.

At the end of the day I want to clear O9 in Sheet1 then move to Sheet2 and reset the functions. The reset in Sheet2 is a Macro which consists of;

Select P2
Ctrl + C
Enter
Ctrl + C
Enter

I don't know why the functions need to be reset in this way but I know that the above procedure works.