Assume the source sheet is "Sheet1".
Assume the destination sheet is "Sheet2", the destination cell is B9.

Put the following macro in the "Sheet1" worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim s1 As Worksheet
Dim s2 As Worksheet
Dim r1 As Range, r2 As Range
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
Set r1 = s1.Range("A:A")
Set r2 = s2.Range("B9")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Target.Offset(0, 3).Copy r2
End Sub

Whenever a cell in column A is selected, the equivalent cell in column D is
copied to the destination cell in the other worksheet.

REMEMBER: the worksheet code area, not a standard module.
--
Gary''s Student - gsnu200748


"davey-in-the-navy" wrote:

is it possible to make a formula (or macro) so if a cell from column "A" is
selected, then a cell from that same row will be copied to another sheet?
example:
A3 in sheet 1 is selected, D3 from sheet 1 is automaticly copied to wherever
specified in sheet 2
I would like to set it up so only 1 cell in column A can be selected at a
time.

I greatly appreciate any help.