Aqui está um método simples e curto.
Você precisará de um contador para incrementar a cada pressionamento de botão. Você precisará colocar isso em algum lugar da sua planilha. Nesse exemplo, ele está abaixo do botão.
- Primeiro decida onde seu contador irá, pois este exemplo estará diretamente abaixo do botão.
Código
Sub Button1_Click()
Dim CopySheet As Worksheet, PasteSheet As Worksheet
Dim xFrom As Integer, xTo As Integer, i As Integer
Dim pasteCell As String, cCell As String
'Sheets
Set CopySheet = Worksheets("Sheet2") 'Sheet you are copying from.
Set PasteSheet = Worksheets("Sheet1") 'Sheet you are pasting into.
'Rows, range of rows start from row rStart to rEnd
rStart = 1 'Start of Row you want to copy from.
rEnd = 10 'End of Row you want to copy from.
'Cells
pasteCell = "B5" 'Cell we will paste data from CopySheet.
'Counter will increments with each button press.
cCell = "E5" 'Change "E5" to reference cell on your spreadsheet.
i = Range(cCell).Value
Application.ScreenUpdating = False 'We disable Screen Updating to prevent interruption.
'Update Counter
i = i + 1
If (i > rEnd) Then
i = rStart
End If
Range(cCell).Value = i
'Copy/Paste Functions
CopySheet.Select
Range("A" & i).Select
Selection.Copy
PasteSheet.Select
Range(pasteCell).Select
ActiveSheet.Paste
Application.ScreenUpdating = True 'Enable Screen Updating at end of operation.
End Sub
Obotãoserácopiadocombasenonúmerodocontadormais1,porisso,seonúmerofor0nobotão,pressioneamacroparaadicionaro0+1einicieasfunçõesdecopiarecolar.