General discussion


Copy cell content to another worksheet

By mail ·
I need to enter data into the first worksheet in Excel and then select data from thre of the cells on the same row and add them to a new line in a second sheet in the same workbook.

eg - on first sheet I enter Name into A1, Age into B1, Postcode into C1.

I want to automatically add Name(A1) and Postcode(C1) to worksheet 2 in the first available row which should be row 1.

The next time I add data into first worksheet it will do the same but will add it to row 2 on sheet 2 and so on.

I have tried the code below, but can only get it to work for one cell.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
' Sheet2 and Sheet3 intentionally updated separately
With Worksheets("Sheet2")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value _
= Target.Value
End With
With Worksheets("Sheet3")
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value _
= Target.Value
End With
End Sub

Any help appreciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by BlitzSonik In reply to Copy cell content to anot ...

I don't know about your code but here is a simple solution with only one flaw that I can see.

On sheet 2, in cell A1 type "=Sheet1!A1" and then in cell C1 type "=Sheet1!C1" without the quotes. This will copy the contents of that cell in Sheet 1 to its corresponding cell in sheet 2.

The flaw being that you would have to then click in the lower right hand corner of the first cel and drag the formula down to the cells below. Maybe even with your coding ability you could create a code that does that for you.

I hope this helps.

Related Discussions

Related Forums