Tags Tags

Simple Copy Paste within Worksheet Change Event

August 24th, 2012 - 11:40 pm ET by willwonka | Report spam
Help Create a new topic
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)

-

Can I not copy to something that is outside of the Intersect Range?

Range("CopyRow") is about a 1 row, 10 column block of cells.. (really just blank cells with formatting and valadations built in).
email Follow the discussionReplies 2 repliesReplies Make a reply

Similar topics

Replies

#1 Peter T
August 25th, 2012 - 10:22 am ET | Report spam
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" wrote in message
news:
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)

-

Can I not copy to something that is outside of the Intersect Range?

Range("CopyRow") is about a 1 row, 10 column block of cells.. (really just
blank cells with formatting and valadations built in).
Replies Reply to this message
#2 willwonka
August 25th, 2012 - 01:39 pm ET | Report spam
Thanks.. Adding the sheet did the trick.

Details, shchmetails.
email Follow the discussion Replies Reply to this message
Help Create a new topicReplies Make a reply
Search Make your own search