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,362
Office Version
365
Platform
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
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,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top