Forum Discussion
When a value changes it then is transfered to another sheet but also retains each change
If N11 is a formula and you believe the calculate event will work better for you, then maybe this is closer to what you want.
Private Sub Worksheet_Calculate() Dim source As Range Dim lastCell As Range Set source = Me.Range("N11") With Worksheets("Sheet2") Set lastCell = .Cells(.Rows.Count, 1).End(xlUp) End With If lastCell.Value <> source.Value Or (IsEmpty(lastCell) Xor IsEmpty(source)) Then lastCell.Offset(1, 0).Value = source.Value End If Set source = Nothing Set lastCell = Nothing End Sub
Right click on sheet1 and select view code. Then, paste this event handler into the code module that appears.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("N11").Address Then
With Worksheets("Sheet2")
.Cells(.Rows.Count, 1).End(xlUp)(2, 1).Value = Target.Value
End With
End If
End Sub
Sorry for this question, new to using code for excel and writing a personal sheet. When I click "View Sheet1 Code" I paste it in. My question is how do I get the code to run? I looked it up and I can't seem to get it to take effect.
EDIT: So I figured out this is a automatic code and it applies whenever the Worksheet_change is in effect but it appears it's not working I think, currently nothing changed in sheet2. Also I tried to look up more fixes, apparently formulas don't affect Worksheet_change but Worksheet_calculate does but when I do that I get an error when a number changes "Compile Error" Procedure declaration does not match description of event or procedure having the same name.
Thank you for the reply!
If N11 is a formula and you believe the calculate event will work better for you, then maybe this is closer to what you want.
Private Sub Worksheet_Calculate() Dim source As Range Dim lastCell As Range Set source = Me.Range("N11") With Worksheets("Sheet2") Set lastCell = .Cells(.Rows.Count, 1).End(xlUp) End With If lastCell.Value <> source.Value Or (IsEmpty(lastCell) Xor IsEmpty(source)) Then lastCell.Offset(1, 0).Value = source.Value End If Set source = Nothing Set lastCell = Nothing End Sub- So, N11 is a formula? And if the value changes when it is calculated, then you want the new value to be copied to sheet2?
