How to put my answer in next row without overwriting my previous answer in VBA-Macro in Excel 2016?

Question

I have recorded a Macro in Excel 2016, however whenever I run this Macro it overwrite my previous answer, how should I put my new answer to next row?

Sub update()
'
' update Macro
'

'
    Range("B4:G4").Select
    Selection.Copy
    Sheets("Sheet4").Select
    Range("B2").Select
    ActiveSheet.Paste
    Sheets("Sheet3").Select
    Range("B7:G7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    Range("B3").Select
    ActiveSheet.Paste
End Sub

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). You should use this technique afer **every** macro recording to improve the macro in speed and reliabilty. — Pᴇʜ, Sep 04 '19 at 05:58

score -1 · Answer 1 · answered Sep 04 '19 at 05:52

Just add lastRow1 line to set always lastRow +1 (empty cell)

Sub updateM()

    Dim lastRow1 As Long

    lastRow1 = Sheets("Sheet4").Cells(Rows.Count, 2).End(xlUp).row + 1
    Range("B4:G4").Copy Sheets("Sheet4").Range("B2")
    Sheets("Sheet3").Range("B7:G7").Copy Sheets("Sheet4").Range("B3").Select

End Sub

score -1 · Answer 2 · answered Sep 04 '19 at 06:30

This should do it. You should try to a void Select or Activate as Pᴇʜ said, here is an example on how to do it:

Option Explicit
Sub update()
'
' update Macro
'

'
    Dim Answer1 As Range, Answer2 As Range

    With ThisWorkbook.Sheets("sheet3")
        Set Answer1 = .Range("B4:G4") 'set the range for the first answer
        Set Answer2 = .Range("B7:G7") 'set the range for the second answer
    End With

    Dim LastRow As Long
    With ThisWorkbook.Sheets("Sheet4")
        'Copy the first answer
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1 'calculate next blank cell
        Answer1.Copy .Cells(LastRow, 2)
        'Copy the second answer
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1 'calculate next blank cell
        Answer2.Copy .Cells(LastRow, 2)
    End With

End Sub

How to put my answer in next row without overwriting my previous answer in VBA-Macro in Excel 2016?

2 Answers2