Jul 22 2020 12:53 PM
So if N11 from sheet1 changes then it's posted to A2 in sheet2 but then next input from N11 would go to A3 and so on.
Jul 22 2020 03:04 PM - edited Jul 22 2020 03:04 PM
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
Jul 22 2020 06:31 PM - edited Jul 22 2020 07:43 PM
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!
Jul 22 2020 11:29 PM
Jul 23 2020 05:46 AM
Jul 23 2020 06:07 AM
Solution
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
Jul 23 2020 01:22 PM
Jul 23 2020 01:25 PM
Jul 23 2020 02:29 PM
Yeah that was on my part, and I changed the formula around a bit with the table which immediately had it outputted in the next sheet so probably was a mix of both but not 100%. Thank you for the assistance!
Jul 23 2020 06:07 AM
Solution
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