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
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- I am guessing the target worksheet was named something other than "sheet2?" I'm glad to see you got it working.