Transferring data from one worksheet to another automatically

[Yendorian]

Using excel 2003.
I would like to have data that is typed into cells in Sheet 1 automatically
transferred to Sheet 2 where the data can then be worked on. The idea is that
the person entering the data on Sheet 1 does not have access to the eventual
calculations on sheet 2.
A simplified example:
The first time, data is entered into Sheet 1 Column B as follows
Column A Column B
Row 1 Surname "Brown"
Row 2 Forename "Richard"
Row 3 Address "21 High Street" etc

and is then automatically transferred to Sheet 2 Row 1 as
Column A Comlumn B Column C etc
Row 1 Brown Richard 21 High Street etc
where it remains unchanged

Then, on the second occasion, data is entered into the same Sheet 1 Column B
(which still contains the "Brown" data) as follows:
Column A Column B
Row 1 Surname Brown replaced by "Smith"
Row 2 Forename Richard replaced by "Michael"
Row 3 Address 21 High Street replaced by "57 Carlton Gardens"
etc

and is then transferred to the next empty row (2) in Sheet 2 as follows:
Column A Comlumn B Column C etc
Row 1 Brown Richard 21 High Street
Row 2 Smith Michael 57 Carlton Gardens
where it too remains unchanged.

Third time, Sheet 1
Row 1 Surname Smith replaced by "Daniels"
Row 2 Forename Michael replaced by "Jack"
Row 3 Address 57 Carlton Gardens replaced by "16 The Avenue"

which gives on Sheet 2:

Column A Comlumn B Column C etc
Row 1 Brown Richard 21 High Street
Row 2 Smith Michael 57 Carlton Gardens
Row 3 Daniels Jack 16 The Avenue

Can anyone suggest a macro that can do this, please?
All help gratefully received.

 

[Yendorian]

Hi,
This works well and I have managed to adapt it to my workbook.
However, I have found that if I leave one item of input data empty, this
cell is filled by info from the next record. For example, taking my previous
example:
If I input on Sheet 1 the third entry as
Row 1 Surname Daniels
Row 2 Forename
Row 3 Address 16 The Avenue
this gives
Column A Comlumn B Column C etc
Row 1 Brown Richard 21 High Street
Row 2 Smith Michael 57 Carlton Gardens
Row 3 Daniels 16 The Avenue

If I then enter on Sheet 1 the fourth entry as
Row 1 Surname Smithson
Row 2 Forename Wilhelm
Row 3 Address 97 Broadway

this gives
Column A Comlumn B Column C etc
Row 1 Brown Richard 21 High Street
Row 2 Smith Michael 57 Carlton Gardens
Row 3 Daniels Wilhelm 16 The Avenue
Row 4 Smithson 97 The Broadway

"Wilhelm" has searched for the previous empty cell in its column thus giving
an incorrect entry.
Sorry about the complication but can you think of a way round this, please?
Thanks in advance