Excel help with Macro code in excel

[Looby]

Hi,
I am using excel 2007. I have some cells in sheet 1 (made to look like a form) and i have created a macro to take the data from these cells and put them in a row on sheet 2. However, what i cannot figure out it what command i need to insert (and where) to have the data go to the next empty row.
This is what i have recorded on the macro:
Range("D9").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D13").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("D2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("E2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D21").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("G2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D23").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("H2").Select
ActiveSheet.Paste
End Sub

Any help much appreciated!

 

[Paul Morgan]

Copy selected cells to next sheet

This will work for you

Sub move_info()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim Rng As Range, c As Range, x, Rws As Long

    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Rws = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng = ws1.Range("D9,D11,D13,D15,D17,D19,D21,D23")
    x = 1

    For Each c In Rng.Cells

        ws2.Cells(Rows.Count, x).End(xlUp).Offset(1, 0) = c
        x = x + 1

    Next c

End Sub

Range Selection Codes