Copy cells onto another sheet

Copper Contributor

If I type a number into cell A1 on sheet one so the cell A1 on sheet two copies, is there a formula for the next day, to change the number in cell A1 on sheet one and the cell A1 on sheet two stays the same as the previous day but cell A2 on sheet two updates to the new figure in cell A1 on sheet one?

Thanks

5 Replies

@ShellLA 

No, formulas don't work that way.

Do you want sheet 2 to reflect every change in the value of A1 on sheet 1, even if there are multiple changes per day?

Thanks for replying.
Yes.
The data is always inputted in A1 on sheet 1 and this data can be updated hourly or daily, dependent on when the new data is available, I then need this data to autofill A1 on sheet 2.
However once the data is updated within A1 sheet 1, then this autofill's A2 on sheet 2, without affecting the original data on within A1 on sheet 2 and so on....

Hope that makes sense and again thanks for help

@ShellLA 

This requires VBA , so it will only work in the desktop version of Excel for Windows and Mac.

Right-click the sheet tab of Sheet 1.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        If Range("A1").Value <> "" Then
            With Worksheets("Sheet 2")    ' modify name as needed!
                On Error Resume Next
                Set rng = .Range("A:A").Find(What:="*", SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Offset(1)
                On Error GoTo 0
                If rng Is Nothing Then
                    Set rng = .Range("A1")
                End If
                rng.Value = Range("A1").Value
            End With
        End If
    End If
End Sub
Thanks again for the help and I've done what you advised

Do I now need to input something different within the required cell range for this VBA to work?

@ShellLA 

If you edit the value of A1 on Sheet 1, you should see the new value on Sheet 2. Don't forget to change Sheet 2 to the real name of that sheet in the code.