Excel VBA Add Data Selected to Row on Another Sheet

Question

I'm very new to Excel VBA. I have a sheet with 4 Dropdown Lists in cells A21:D21 in a sheet named "Summary". I'm trying to create a button function that when clicked inserts the 4 selected values in the dropdowns into a new row (Preferably the first empty row) on another sheet called "Data".

This is the function I currently have written:

Sub Add_Button()
    Summary.Range("A21:D21").Copy
    Data.Range("A2:D2").Rows("1:1000").Insert Shift:=xlDown
End Sub

This is the error I am getting:

Any help would be greatly appreciated!

Answer 1

Your question is not so clear and you did not answer the clarification questions...

If you want inserting at the end of the "Data" sheet, please use the next code:

Sub Add_Button()
  Dim Summary As Worksheet, data As Worksheet, lastRD As Long
    
    Set Summary = Worksheets("Summary") 'use here your real sheet
    Set data = Worksheets("Data")       'use here your real sheet             
    lastRD = data.Range("A" & data.rows.count).End(xlUp).row + 1 'last empty row
    
    data.Range("A" & lastRD).Resize(1, 4).Value2 = Summary.Range("A21:D21").Value2
End Sub

If you want inserting the copied range on a specific row, please use the next variant:

Sub Add_ButtonX()
  Dim Summary As Worksheet, data As Worksheet, insertRow As Long
    
    Set Summary = Worksheets("Summary") 'use here your real sheet
    Set data = Worksheets("Data")       'use here your real sheet
    insertRow = 20  'rows on which to insert copied data
    Summary.Range("A21:D21").Copy
    data.Range("A" & insertRow).Insert Shift:=xlDown
End Sub

To paste the values in the first empty row, please use this one:

Sub Add_ButtonInsEmptyRow()
  Dim Summary As Worksheet, data As Worksheet, frstER As Long
    
    Set Summary = Worksheets("Summary") 'use here your real sheet
    Set data = Worksheets("Data")       'use here your real sheet
    frstER = data.Range("A2").End(xlDown).row + 1 'first empty row

    data.Range("A" & frstER).Resize(1, 4).Value2 = Summary.Range("A21:D21").Value2
End Sub