VBA Copy Value of Selected Cell to Next Blank Cell

[cmangi]

I have a list of names (starting in C21), column B is empty. I am trying to find a macro that will copy value of the selected cell in to the first empty cell in column B (starting at B21), then clear the original cell.
To make it a little more clear see example below.

Lets say I selected C22 (John) and ran the macro. It would copy "John" and place it in column B starting at row 21. Then if I selected C23 (Scott) and ran the macro it would copy "Scott" and place it in the next open slot, which in this example would be B22

	B	C
21		Scott
22		John
23		Frank
24		Joe

<tbody>
</tbody>

I am pretty new at macros and have only recorded macros, so I am a bit lost. Any help would be greatly appreciated.

Thanks,

 

[onlyadrafter]

Hello,

if you only copy to the cell to the left, then try

Sub COPY_OVER()
    MY_CELL = ActiveCell.Address
    ActiveCell.Copy
    If Range("B" & Rows.Count).End(xlUp).Row < 21 Then
        Range("B21").PasteSpecial (xlPasteAll)
    Else
        Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    End If
    Range(MY_CELL).ClearContents
End Sub

 

[kalak]

Perhaps try

Sub hfg()
With Selection
    If Not .Column = 3 Then MsgBox "Please select in Col C": Exit Sub
    If Range("B21") = "" Then
       .Cells.Cut Range("B21")
    Else
        .Cells.Cut Range("B" & Rows.Count).End(xlUp)(2)
    End If
End With
End Sub

 

[cmangi]

Perhaps try

Sub hfg()
With Selection
    If Not .Column = 3 Then MsgBox "Please select in Col C": Exit Sub
    If Range("B21") = "" Then
       .Cells.Cut Range("B21")
    Else
        .Cells.Cut Range("B" & Rows.Count).End(xlUp)(2)
    End If
End With
End Sub

This macro works great, thanks. What would I add to make it clear the row of the table that was selected. My table is C through K, but once I copy the C value I would like to clear the the remaining cells in that row of the table.

For example if I run the macro on a selected name, lets say c26 I want to delete the range of the row C26:K26.

Any ideas?

 

[kalak]

For example if I run the macro on a selected name, lets say c26 I want to delete the range of the row C26:K26.
Any ideas?

Like this?

Sub kal()
With Selection.Resize(1)
    If Not .Column = 3 Then MsgBox "Please select in Col C": Exit Sub
    If Range("B21") = "" Then
       .Copy Range("B21")
    Else
        .Copy Range("B" & Rows.Count).End(xlUp)(2)
    End If
    .Resize(, 9).ClearContents
End With
End Sub

 

[Rick Rothstein]

I think this macro will also work...

Sub CopySelectedCellToFirstBlankAfterB20() If ActiveCell.Column = 3 Then Range("B21")(Application.Max(21, Cells(Rows.Count, "B").End(xlUp)(2).Row) - 20).Value = ActiveCell.Value ActiveCell.Resize(, 9).ClearContents End If End Sub

 

[cmangi]

Thank you guys for all your help.

Rick I ended you using your formula. Works great.