I am trying to copy a contents of cell to the other tab in the same
spreadsheet and written the macro as below.

Sub AddComment()
'
' AddComment Macro
' Macro recorded
'
Selection.Copy
Sheets("Comments").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Range("A4").Select
ActiveSheet.Paste
Range("B4").Select
End Sub

What it is doing is copy the 'selected cell' in the sheet only.
As my spreasheet is very wide, I want to be able to run the macro from any
cell in a perticular row and copy a specific cell contents (e.g. contents
from column A for the selected row)

Help is much appriciated.
Thanks.