copying results from one sheet and paste in next available cell in another

[sl1990]

Hi ive got 150 sheets (named sheet1-150) in each of these ive got the results of a macro ive run to determin a rank.

the results are in z2,z4,z6 all the way to z20. i also want to copy the cell a1.

i then want to paste these in a sheet called results, in the A collum, i would like the sheet1 results to pasted into A1 and then the sheet2 results into the next available cell in the A collum

any idea how i would do this ?

 

[VoG]

Try

Sub test()
Dim i As Long
For i = 1 To 150
    With Sheets("Sheet" & i)
        .Range("A1").Copy Destination:=Sheets("results").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Range("Z2:Z20").SpecialCells(xlCellTypeConstants).Copy Destination:=Sheets("results").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End With
Next i
End Sub