0

I am recently new to VBA and admit I am a novice. The procedure that I require is that a 'Button' is selected. The code is ran and the result is that a new row (which is a copy of the row above and contains several variables and data validations) is input below the last row. For a little context, "NEW" is selected, for every time the user has to fill in a new "Situation" BUT the new row must ALWAYS appear below the last entry; if that makes sense

Here is the macro code thus far:

Sub Macro6()
'
Application.ScreenUpdating = False

    Range("A2:F2").Select
    Selection.Copy
    Range("A5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Application.ScreenUpdating = True

End Sub

Sorry for any novel mistakes, I only started VBA around 20 mins ago. I did ask a fellow colleague for help, however, we could not crack it

Regards

Vityata
  • 42,633
  • 8
  • 55
  • 100
MATTHEW HOLLIS
  • 11
  • 2

1 Answers1

1

This is something, that will make your code workable the way you want it:

Sub Macro6()

    Dim lastRow As Long

    Application.ScreenUpdating = False
    lastRow = 1 + Cells(Rows.Count, "A").End(xlUp).Row

    Range("A2:F2").Select
    Selection.Copy

    Range("A" & lastRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

As tips for next steps, once you have spent more than 20 minutes on VBA, try to avoid Select and Activesheet - How to avoid using Select in Excel VBA

Furthermore, when you are working with the macro recorder, make sure to use Ctrl to navigate around the Excel application. It will usually generate good results.

Vityata
  • 42,633
  • 8
  • 55
  • 100