Worksheet Macro crashes

[steve case]

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]

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:

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]

Does this work?

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]

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]

Are you sure it works?

I think I might have made quite a daft mistake.

Instead of this.

Target.Copy

you might need this

ActiveCell.Copy

 

[Von Pookie]

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.

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

If Target.Count > 1 then Exit Sub

as the first line of the macro.

Also, you can add

Application.CutCopyMode=False

after it pastes to Sheet2 to remove the moving dotted line around the cell that was copied.

 

[steve case]

Hmmmm too late now I didn't see your post until after I posted the second part.

At any rate, I'm happy. 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. I'll probably put up a "Repeat" button. Problem solved. :wink:

Have a great Thanksgiving & Merry Christmas.

 

[Norie]

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.

Don't know why but I get confused with ActiveCell and Target in the SelectionChange.

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

 

[steve case]

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!