Transferring data from one worksheet to another automatically

[Guest]

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.

 

[Guest]

Hey there, this is a very simple macro I just threw together. You can work
with it as much as you like to make it more efficient for this is not very
good code writing. But it works. Another think to keep in mind is it will
give you an error if there is no value in sheet 2 on row 1. So Id put in some
headers like Surname, Forename, Address. Then it will work whenever you run
the macro or click a button. Let me know if you have any questions...

Sub test()
Application.ScreenUpdating = False
Sheets(1).Range("B1").Copy
Sheets(2).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

Sheets(1).Range("B2").Copy
Sheets(2).Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial

Sheets(1).Range("B3").Copy
Sheets(2).Range("C65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.ScreenUpdating = True

End Sub

 

[Guest]

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