07-22-2020 12:53 PM
07-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.
07-22-2020 03:04 PM - edited 07-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
.Cells(.Rows.Count, 1).End(xlUp)(2, 1).Value = Target.Value
07-22-2020 06:31 PM - edited 07-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!
07-22-2020 11:29 PM
07-23-2020 05:46 AM
07-23-2020 06:07 AMSolution
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
07-23-2020 01:25 PM
07-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!