Forum Discussion
ShellLA
Jul 07, 2024Copper Contributor
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 t...
ShellLA
Jul 07, 2024Copper Contributor
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
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
HansVogelaar
Jul 07, 2024MVP
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- ShellLAJul 07, 2024Copper ContributorThanks 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?- HansVogelaarJul 07, 2024MVP
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.