How do I create a command button, that will replace cell values- based on selections made in previous column?

Question

I want to be able to take the Data validation selected in Column A and paste those selections into Column B when I press command button

New Leader	Old Leader
Example Name	Tim Jones
	Al Smith
	Al Smith
Example Name	Tim Jones
	Ray Fines
Example Name	Ray Fines
	Jan Smith
	Jan Smith

I press the button and the list in column B changes to

Old Leader
Example Name
Al Smith
Al Smith
Example Name
Ray Fines
Example Name
Jan Smith
Jan Smith

How can I code the command button to do this?

Answer 1

Assign the following macro to a command button from the Form Controls section:

Sub MoveData()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    For r = 2 To m
        If Range("A" & r).Value <> "" Then
            Range("B" & r).Value = Range("A" & r).Value
        End If
    Next r
    Application.ScreenUpdating = True
End Sub