Tenho uma macro que funciona bem...
Ela copia onde tem dados, se for seu caso, apenas mude os nomes...
Espero ter ajudado
[]´s
Jaime
Sub Macro3()
'
' Macro3 Macro
'
If MsgBox("TODOS OS DADOS SERÃO DEFINITIVAMENTE APAGADOS, CONFIRMA??", vbYesNo, "ATENÇÃO, AVISO IMPORTANTE!!!") = vbYes Then
Range("B8:H8").Select ' Seleciona as celulas no meu caso, é a coluna B
Range(Selection, Selection.End(xlDown)).Select ' Seleciona até onde possui dados
Selection.Copy ' Copia
Sheets("Nome da guia da sua planilha").Select ' Seleciona planilha para colar
Range("A1").Select
If Range("A2") = "" Then
ActiveCell.Offset(1, 0).Select
Else
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
End If
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False ' Cola com os formatos proprios
Range("A2").Select ' Seleciona celula
Sheets("nome sua guia").Select ' Seleciona planilha inicial
Range("B8").Select ' Seleciona celula
Application.CutCopyMode = False ' Limpa area de transferencia
Range("B8:D100").Select ' Seleciona celulas de onde foram copiados os dados
Selection.ClearContents ' Limpa as celulas
Range("B8").Select ' Seleciona celula para novas entradas
End If
End Sub