Forum Discussion
Copy cells onto another sheet
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
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 helpThis 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
