You need to specify workseet. Change line
If Worksheet.Cells(i, 1).Value = "X" Then
to
If Worksheets("Sheet2").Cells(i, 1).Value = "X" Then
UPD:
Try to use following code (but it's not the best approach. As @SiddharthRout suggested, consider about using Autofilter):
Sub LastRowInOneColumn()
Dim LastRow As Long
Dim i As Long, j As Long
'Find the last used row in a Column: column A in this example
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox (LastRow)
'first row number where you need to paste values in Sheet1'
With Worksheets("Sheet1")
j = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
For i = 1 To LastRow
With Worksheets("Sheet2")
If .Cells(i, 1).Value = "X" Then
.Rows(i).Copy Destination:=Worksheets("Sheet1").Range("A" & j)
j = j + 1
End If
End With
Next i
End Sub