Copy contents of cell to another tab

[Beginner for Macro]

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.

 

[Peter T]

I don't quite follow what you are trying to do, but maybe you can adapt this
example to your needs

Sub test()
Dim rngSource As Range
Dim rngDest As Range

Set rngSource = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
Set rngDest = ActiveWorkbook.Worksheets("Sheet2").Range("B2")

rngSource.Copy Destination:=rngDest

End Sub

rngDest should be same size as rngSource or it can be a single cell

If you only want to paste values, you could use the PasteSpecial function
but you can also do this

rngDest.Value = rngSource.Value

In passing, although the macro recorder faithfully records your Select and
Activate keystrokes you seldom need to use Select or Activate in your actual
code.

Regards,
Peter T

 

[Beginner for Macro]

Sorry, I should have explained a lil bit more.

What I have is a database very wide and long.
The range for the code example below looks like this
Column A : Header A1 = Country, A2:A? = Country names
Column B : Header B1 = Name, B2:B? = Names
Column C : Header C1 = Gender, C2:C? = F or M
Column D : Header D1 = Birthday, D2? = Dates
Column ...: ......
Column XXX : Header XXX1 = Last Update, XXX2:XYZ? = Dates

I want to be able to run a macro while on XXXn (or any other cell in that
row) but copy the contents from Bn (contents in column B for the same row,
i.e. B2 if the selection is anywhere in row 2 and B5 if the selection is
anywhere in row 5) to anoother Tab in the same spreadheet.

The problem I am having with the macro written above (in the original
posting) is it copys the contents from XXXn (whatever the other cell selected
before running that macro) and not from column 'B'.

I hope this is useful and a bit more clear.
Thanks in advance.