Auto-inserting text from one worksheet to another

[bobbyd98682]

I’m not certain how to ask my question, so I’ll state where I need help, give an example and follow with the question.

I enter data into rows on Sheet 1, with each following row (2, 3, 4, etc.) representing a new line of data. I’d like each new line of data to be auto-inserted , over-written in the same place, on Sheet2.

Example:
The word ‘Unicorn’ is entered into cell A1 Sheet1. Into cell A1 Sheet2, the formula ‘=Sheet1!A1’ is entered, and the word Unicorn appears in cell A1 Sheet2.

Is there a way, that when I enter ‘Dragon’ into cell A2 Sheet1, ‘Dragon’ will appear in cell A1 Sheet2, overwriting the previous contents, without having to change the formula in cell A1 Sheet2 each time I enter a new line of data?

In other words, whatever new thing I write in the next row on Sheet1, gets copied to the anchor cell A1 Sheet2.

Perhaps a new formula needs to be inserted into cell A1 Sheet2, or a macro can be written?

Any help would be greatly appreciated.

 

[vletm]

something like this?

 

[bobbyd98682]

something like this?

This inserts at a 1:1 ratio. That is, whatever I enter on Sheet1, in any cell, is inserted in the same cell address on Sheet2.
That is, if I enter 'hello' in Sheet1 cell D3, 'hello' is copied to Sheet2 cell D3.

I need the last string, written in any cell in column A Sheet1, to be copied to cell A1 Sheet 2. Is this possible?

 

[Nebu]

Hi:

Try using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With Sheet1
    ay& = ActiveCell.Row
    ax& = ActiveCell.Column
    i& = Selection.Rows.Count
    j& = Selection.Columns.Count + ax - 1
    If i = 1 Then i = ay
    If j = 1 Then j = ax
    Sheet2.Range(Sheet2.Cells(ay, ax), Sheet2.Cells(i, j)).Value = .Range(.Cells(ay, ax), .Cells(i, j)).Value
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Thanks

 

[vletm]

@bobbyd98682
Something like this? All to Sheet1's A1.

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Cells(Target.Row, Target.Column)
End Sub

Just replace above code to my previous version...