Worksheet Macro crashes

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
788
I want to have a range of cells on Sheet1 for the user to click on and have his selections pasted on Sheet2 down Column A in the order he clicked on them.

I have the following Worksheet code:

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

Doesn't work! it hangs up on:

Range("A65536").End(xlUp).Select

So I tried:

Columns("A:A").Select
Selection.Find(What:=Empty, After:=ActiveCell).Select


It didn't like that either.

What am I doing wrong?

Can anybody help?

I'm a noob to this workshet macro stuff!
.
.
.
.
.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
At the moment, I'm not sure why that line isn't working...but I do know you can do this without having to select the cells (other than the one you're copying, of course ;) ). This works for me:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.Copy
Sheets("Sheet2").Range("A65536"). _
End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues

End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Does this work?

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim NextRow As Long

    NextRow = Sheets("Sheet2").Range("A65536").End(xlUp).Row
    Target.Copy
    Sheets("Sheet2").Range("A" & NextRow).PasteSpecial Paste:=xlValues
    
End Sub
 

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
788
Yes that* works

Hugs & stuff!

Less than 15 minutes for an answer! this is the most valuable place on the net.

*Pookie's (Cross post with Norie didn't see it in time)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Are you sure it works?:)

I think I might have made quite a daft mistake.:oops:

Instead of this.
Code:
Target.Copy
you might need this
Code:
ActiveCell.Copy
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Using Target instead of Activecell shouldn't make a bit of difference, in my book. That's what the Target variable is there for, after all. :unsure:

Although, if you want to make sure it doesn't copy the cells when more than 1 are selected, just add

Code:
If Target.Count > 1 then Exit Sub
as the first line of the macro.

Also, you can add
Code:
Application.CutCopyMode=False
after it pastes to Sheet2 to remove the moving dotted line around the cell that was copied.
 

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
788
Hmmmm too late now I didn't see your post until after I posted the second part.

At any rate, I'm happy. :biggrin: I'm updating an old file that is sort of clumsy for the user. Instead of selecting and then pressing F1 (it's a macro) now all they will have to do is point and click.

It's a production scheduling thing that puts together a list of products to build for a segment of the shift.

As I type this I realize that multiples in a row might be a problem. If they want two in a row of whatever is in Cell B2 for example. :rolleyes: I'll probably put up a "Repeat" button. Problem solved. :wink:

Have a great Thanksgiving & Merry Christmas. :)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Von Pookie said:
Using Target instead of Activecell shouldn't make a bit of difference, in my book. That's what the Target variable is there for, after all. :unsure:
Don't know why but I get confused with ActiveCell and Target in the SelectionChange. :oops:

Just seemed that with limited testing it wasn't working.
 

steve case

Well-known Member
Joined
Apr 10, 2002
Messages
788
The file has been successfully updated, and the guys on the floor who use it are happy as it runs faster now.

Thank you all!
 

Forum statistics

Threads
1,078,400
Messages
5,339,991
Members
399,346
Latest member
t0land

Some videos you may like

This Week's Hot Topics

Top