Simple Copy Paste within Worksheet Change Event

[willwonka]

I have some code that obviously works outside a Worksheet Change Event.

Range("CopyRow").Copy Destination:=Sheets("Current Task LIST").Cells(651, 1)

When I put in a change event, I get the dreaded Run-time error '1004'. Method 'Range' of object'_Worksheet'

Here is the first part of code:

Private Sub Worksheet_Change(ByVal Target As Range)

'On Error Resume Next

Dim OLook As Object 'Outlook.Application
Dim Mitem As Object 'Outlook.Mailitem
Dim MsgBody As String
Dim Trow As Integer

Dim SendAnEmail As Boolean

If Not Intersect(Target, Range("a2:a2000")) Is Nothing Then

Application.EnableEvents = False

Range("CopyRow").Copy Destination:=ActiveSheet.Cells(Target.Row, 1)

 

[Peter T]

I assume CopyRow does refers to a named range in some other sheet. In a
worksheet module all Range/Cell references, unless otherwise qualified,
refer to the object module's sheet.

If you know the name of the named range's sheet you can do this -

Worksheets("mySheet").Range("CopyRow").Copy etc

If you don't know the name, eg it's subject to change by user, you can do
this -

Dim nm as Name
' (assume a workbook level name)
Set nm = ActiveWorkbook.Names("CopyRow")
nm.RefersToRange.Copy etc

Regards,
Peter T

 

[willwonka]

Thanks.. Adding the sheet did the trick.

Details, shchmetails.