Copy cells to a new row in another sheet

Question

I need to copy a few cells from a form on sheet Form, then paste them into a new row on sheet Activities.

Breaking it down:

When the button is clicked:

• The cells "B2,B3,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22" will be selected on the active sheet (Form) and copied.
• The copied cells are pasted on another sheet (Activities) and pasted on a new row (something like A + 1)

This is what I have so far:

Private Sub CommandButton1_Click()
    Sheets("Form").Select
    Range("B2,B3,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22").Select
    Selection.Copy
    Sheets("Activities").Select
    If Sheets("Activities").Range("A9") = "" Then
        Sheets("Activities").Range("A9").PasteSpecial Paste:=xlPasteValues
    Else
        Sheets("Activities").Range("A10").Select
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues
    End If
End Sub

But it's not working properly, and I haven't managed to figure out the A+1 yet.

Answer 1

First of all, You should avoid Select statement if it's not necessary (it raises events unnecessarily, etc.).

My approach would be:

Dim rng As Range
Set rng = Sheets("Novo Pedido").Range("B2,B3,B4,B5,B6,A10,A16,A21,A24,E10,E17,E20,E23,E26,I10,I12,I14,I16,M10,M12,M14,M16,M19,M22")

For Each cell In rng
    'here you copy to another sheet, one row lower
    Sheets("Geral").Cells(cell.Row + 1, cell.Column).Value = cell.Value
Next cell