Isso faz o que você quer.
Quando você executa o VBa, não há como desfazer, então faça um backup do seu arquivo primeiro e brinque com uma cópia !!
Por favor, note os comentários no código, isso é coisas que você precisa atualizar
Eu também assumi que você não está usando worksheet2, então este código usa isso!
Option Explicit
Sub SaveAsNewRow()
Dim sourceData As String
sourceData = "A1" 'the column and row from where you want to copy FROM
Dim destinationData As String
destinationData = "B" 'the column from where you want to copy TO
Dim newRow As Integer
newRow = 1
Do While (Range(destinationData & newRow).Value <> "")
newRow = newRow + 1
Loop
Worksheets("sheet1").Range("B" & newRow).Value = Worksheets("sheet1").Range(sourceData).Value
End Sub
Como eu adiciono o VBA no MS Office?