VBA Copy and Pasting 2 Rows Down

Question

I've only just come across VBA so I'm a complete novice. Essentially I'm currently automating a form that requires writing out the questions, potential answers, instructions, etc. for developers on a spreadsheet. I've created a basic template table so all the questions are structured the same. I want to copy and paste this table (clearing the contents and taking off the number of the question) and paste it 2 rows down from the bottom of the last table.

The code works fine if I just wanted to copy and paste the table directly below the first but I can't go any further than that. I'm not sure how to write that I want it to find the last filled in row and paste the table 2 rows below.

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Range("C2:G6").Select
    Selection.ClearContents

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "C"

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

    Range("A8").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). — Pᴇʜ, Sep 11 '19 at 06:33

Miles Fett · Answer 1 · 2019-09-11T13:34:30.253

You can completely avoid using select in order to achieve your goal. In the following code, Source is the range of your table, LastRow finds the last row of your table and DestRng is the destination where you want a copy of your table. Hope this helps!

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Dim TblRng As Range
    Set TblRng = Range("C2:G6")
    TblRng.ClearContents

    Dim Source As Range
    Set Source = Worksheets("Sheet2").Range(("A2"), Range("G2").End(xlDown))
    Source.Copy

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "G").End(xlUp).Row

    Dim DestRng As Range
    Set DestRng = Source(LastRow + 1, "A")
    DestRng.PasteSpecial xlPasteAll

End Sub

VBA Copy and Pasting 2 Rows Down

1 Answers1