Worksheet Macro - limit to a range

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
788
I just got the first part to work here:

http://www.mrexcel.com/board2/viewtopic.php?t=180814&highlight=


Thank you all very much, and the next question is:

Is there a way to limit the range that this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Target.Copy
Sheets("Sheet2").Range("A65536"). _
End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
Sheets("Sheet1").Select
End Sub

will copy?

Point and click in "A1:D10" and it works, outside of that range it doesn't.

Can this be done?

Like I said, I'm a Noob!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
Does this work?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Count > 1 Then Exit Sub
    If Intersect(Range("A1:D10"), Target) Is Nothing Then Exit Sub
    
    Target.Copy
    Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
    
End Sub
By the way did you see my last post in the other thread?

Have you checked that the correct values are being pasted?
 

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
788
Works great!

A big thank you!

With any luck I won't have to come back here for a while.

Yes, I saw your previous post, but I had already used Pookie's which at that stage of the game did the job. There's an edit there that says as much.

You also wrote: "Have you checked that the correct values are being pasted?"

Yes, I put up a little column of formulas =Sheet2!A1 and copied them on down to watch the results.
 

Forum statistics

Threads
1,078,117
Messages
5,338,314
Members
399,226
Latest member
arictarhvasiliev

Some videos you may like

This Week's Hot Topics

Top