Worksheet Macro - limit to a range

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
815
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!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
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
815
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,991
Messages
5,834,769
Members
430,319
Latest member
Excelhelppll

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top