Quick cut and paste ideas

[9iron]

Howdy Y'all

A1:A24 contains a list of players names.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I’m trying to quickly fill team rosters in ColumnH by double-clicking the cells in column A.<o></o>
<o> </o>
So, double-clicking a Column A cell cuts and pastes the name to the first empty cell in Column H.

Can you guys help me do this?
<o> </o>
<o> </o>

 

[Marcelo Branco]

Hi,

Welcome to the forum

Copy the macro below (Ctrl+C), right-click in the sheet-tab, View code and paste (Ctrl+V) in the right-panel.

Try it on a test-workbook

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim LRA As Long, LRH As Long
    
    LRA = Cells(Rows.Count, "A").End(xlUp).Row
    If Not Intersect(Target, Range("A1:A" & LRA)) Is Nothing Then
        Application.EnableEvents = False
        If Range("H1") = "" Then
            LRH = 1
        Else
            LRH = Cells(Rows.Count, "H").End(xlUp).Row + 1
        End If
        Cells(LRH, 8) = Target.Value
        Target = ""
        Application.EnableEvents = True
    End If
End Sub

HTH

M.

 

[SydneyGeek]

Welcome to the Board!

This option doesn't cut and paste (I try to avoid using Cut because of what it does to cell references) but instead highlights any player in Column A whose name already appears in column H.

1. Select the player names, then...
2003 and earlier, Format | Conditional Formatting
Cell Formula is...
=COUNTA($H:$H,A1)>0
Pick your cell highlight color, then OK

2007 and higher, Home | Conditional Formatting
New Rule (near the bottom of the list)
Pick the Formula option
Use the same formula as above, highlight to suit.

2. Right-click the sheet tab and View Code.
3. Paste this code into the blank module that appears...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rwLast As Long
    rwLast = Range("H" & Rows.Count).End(xlUp).Row + 1
    
    If Target.Column <> 1 Then Exit Sub
    If Target.Row > 24 Then Exit Sub
    Cells(rwLast, 8).Value = Target.Value
End Sub

Close the code window. Double-clicking any cell in A1:A24 will transfer that name to the next blank row in Column H.

Denis

 

[9iron]

Thanks Marcelo,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
That’s what I was looking for…I would have never figured that out.<o></o>
<o> </o>
And SydneyGeek,<o></o>
I like your method too…<o></o>
But I can’t get it to work right.<o></o>
<o> </o>
All the cells in Column A turned red with cond formatting<o></o>
When I double-click the names, they change to black font then back to red when I select another cell.<o></o>
Wonder what I'm doing wrong?

 

[Marcelo Branco]

Thanks Marcelo,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
That’s what I was looking for…I would have never figured that out.<o></o>
<o></o>

You are welcome and tks for providing feedback

M.