copy cells value from sheet to another

Hi Speed

Unfortunately, your line 

Target.Value = Sheets("sheet2").Columns(2)

changes the value of the cell you've entered to the value of sheet 2 column B! (Making it blank at present).

If you want any value typed in sheet1 column A to go to sheet2 (even if you are over-typing an existing value in sheet1), this will work:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then

        If Target.Value <> "" Then
            LastCell = Sheets("sheet2").Cells(Rows.Count, 1).End(xlUp).Row
            Sheets("sheet2").Cells(LastCell + 1, 1).Value = Target.Value
        End If

    End If

End Sub

To understand the bit in bold above (which puts the sheet1 value below existing data in sheet 2, see Don's tutorial Get the Last Row using VBA in Excel. To see how to do the same for columns, see Find the Last Column with Data in Excel VBA.

If you're new to VBA,  go to the Tutorials page and find Don's 6 introductory lessons, the first one being Excel Macros Class 1 - Getting Started Programming Macros