SOLVED

When a value changes it then is transfered to another sheet but also retains each change

%3CLINGO-SUB%20id%3D%22lingo-sub-1539410%22%20slang%3D%22en-US%22%3EWhen%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539410%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20if%20N11%20from%20sheet1%20changes%20then%20it's%20posted%20to%20A2%20in%20sheet2%20but%20then%20next%20input%20from%20N11%20would%20go%20to%20A3%20and%20so%20on.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1539410%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539725%22%20slang%3D%22en-US%22%3ERe%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539725%22%20slang%3D%22en-US%22%3E%3CP%3ERight%20click%20on%20sheet1%20and%20select%20view%20code.%20Then%2C%20paste%20this%20event%20handler%20into%20the%20code%20module%20that%20appears.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EIf%20Target.Address%20%3D%20Me.Range(%22N11%22).Address%20Then%3CBR%20%2F%3EWith%20Worksheets(%22Sheet2%22)%3CBR%20%2F%3E.Cells(.Rows.Count%2C%201).End(xlUp)(2%2C%201).Value%20%3D%20Target.Value%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540000%22%20slang%3D%22en-US%22%3ERe%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540000%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20for%20this%20question%2C%20new%20to%20using%20code%20for%20excel%20and%20writing%20a%20personal%20sheet.%20When%20I%20click%20%22View%20Sheet1%20Code%22%20I%20paste%20it%20in.%20My%20question%20is%20how%20do%20I%20get%20the%20code%20to%20run%3F%20I%20looked%20it%20up%20and%20I%20can't%20seem%20to%20get%20it%20to%20take%20effect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%20So%20I%20figured%20out%20this%20is%20a%20automatic%20code%20and%20it%20applies%20whenever%20the%20Worksheet_change%20is%20in%20effect%20but%20it%20appears%20it's%20not%20working%20I%20think%2C%20currently%20nothing%20changed%20in%20sheet2.%20Also%20I%20tried%20to%20look%20up%20more%20fixes%2C%20apparently%20formulas%20don't%20affect%20Worksheet_change%20but%20Worksheet_calculate%20does%20but%20when%20I%20do%20that%20I%20get%20an%20error%20when%20a%20number%20changes%20%22Compile%20Error%22%20Procedure%20declaration%20does%20not%20match%20description%20of%20event%20or%20procedure%20having%20the%20same%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20reply!%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540322%22%20slang%3D%22en-US%22%3ERe%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540322%22%20slang%3D%22en-US%22%3ESo%2C%20N11%20is%20a%20formula%3F%20And%20if%20the%20value%20changes%20when%20it%20is%20calculated%2C%20then%20you%20want%20the%20new%20value%20to%20be%20copied%20to%20sheet2%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540848%22%20slang%3D%22de-DE%22%3ERE%3A%20When%20a%20value%20changes%20it%20then%20is%20transferred%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540848%22%20slang%3D%22de-DE%22%3EN%20function%20description%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fn-function-a624cad1-3635-4208-b54a-29733d1278c9%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2Fn-function-a624cad1-3635-4208-b54a-29733d1278c9%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540908%22%20slang%3D%22en-US%22%3ERE%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540908%22%20slang%3D%22en-US%22%3E%3CBR%20%2F%3EJust%20put%20it%20like%20this%20...%20in%20case%20it%20should%20get%20more%20complicated%20...%20although%20the%20answer%20from%20JMB17%20is%20the%20quickest%20and%20easiest.%3CBR%20%2F%3EFresh%20from%20the%20internet%20address%20...%20with%20a%20little%20adjustment%20it%20could%20work%20...%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20the%20class%20module%20%22This%20workbook%22%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20Workbook_Open()%3CBR%20%2F%3E'Start%20der%20Control-Routine%3CBR%20%2F%3ECtrl_Each_Minute%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E'In%20ein%20Modul%3CBR%20%2F%3EPublic%20CtrlValue%2C%20Mldg%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20Control_each_Minute()%3CBR%20%2F%3E'Variablen%20erstellen%3CBR%20%2F%3EDim%20Qe%20As%20Integer%3CBR%20%2F%3EDim%20Msg%20As%20String%2C%20wks%20As%20String%2C%20myCell%20As%20String%3CBR%20%2F%3E'Prepare%20variables%3CBR%20%2F%3EMsg%20%3D%20%22%22%3CBR%20%2F%3Ewks%20%3D%20%22Tabelle1%22%20''Table%20in%20which%20the%20value%20is%3CBR%20%2F%3EmyCell%20%3D%20%22A1%22%20'Cell%20to%20be%20checked%3CBR%20%2F%3E'The%20first%20time%20the%20variable%20is%20started%2C%20it%20must%20be%20checked%20and%20filled%3CBR%20%2F%3EIf%20IsEmpty(CtrlValue)%20Then%3CBR%20%2F%3ECtrlValue%20%3D%20Worksheets(wks).Range(myCell)%3CBR%20%2F%3EMldg%20%3D%200%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E'Start%20for%20the%20next%20check%3CBR%20%2F%3EApplication.OnTime%20Now()%20%2B%20TimeValue(%2200%3A01%3A00%22)%2C%20%22control_each_Minute%22%3CBR%20%2F%3E'Message%20when%20the%20value%20has%20been%20changed%3CBR%20%2F%3EIf%20Worksheets(wks).Range(myCell)%20%26lt%3B%26gt%3B%20CtrlValue%20Then%3CBR%20%2F%3EMsg%20%3D%20%22Der%20Kontrollwert%20%22%20%26amp%3B%20CtrlValue%20%26amp%3B%20%22%20hat%20sich%20ge%C3%A4ndert.%22%20%26amp%3B%20Chr%24(13)%3CBR%20%2F%3EMsg%20%3D%20Msg%20%26amp%3B%20%22%20Would%20you%20like%20the%20new%20value%20%22%20%26amp%3B%20Worksheets(wks).Range(myCell)%20%26amp%3B%20%22%20as%20a%20control%20value%3F%20%22%3CBR%20%2F%3EIf%20Mldg%20%26gt%3B%200%20Then%3CBR%20%2F%3EQe%20%3D%20MsgBox(Msg%2C%20vbCritical%20%2B%20vbYesNo%20%2B%20vbDefaultButton1%2C%20%22ACHTUNG%3A%20%22%20%26amp%3B%20Mldg%20%26amp%3B%20%22.%20%C3%84%C3%84nderungsinformation%22)%3CBR%20%2F%3EIf%20Qe%20%3D%206%20Then%3CBR%20%2F%3E'Takeover%20of%20the%20new%20value%20as%20a%20control%20value%3CBR%20%2F%3ECtrlValue%20%3D%20Worksheets(wks).Range(myCell)%3CBR%20%2F%3E'Reset%20Counters%3CBR%20%2F%3EMldg%20%3D%200%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E'add%20up%20the%20Counters%3CBR%20%2F%3EMldg%20%3D%20Mldg%20%2B%201%3CBR%20%2F%3EElse%3CBR%20%2F%3EQe%20%3D%20MsgBox(Msg%2C%20vbCritical%20%2B%20vbYesNo%20%2B%20vbDefaultButton1%2C%20%22%20ATTENTION%3A%20Change%20information%20%22)%3CBR%20%2F%3EIf%20Qe%20%3D%206%20Then%3CBR%20%2F%3E'Take%20over%20value%20as%20a%20Control%20value%3CBR%20%2F%3ECtrlValue%20%3D%20Worksheets(wks).Range(myCell)%3CBR%20%2F%3E'ResetCounters%3CBR%20%2F%3EMldg%20%3D%200%3CBR%20%2F%3EExit%20Sub%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E'Add%20up%20Counters%3CBR%20%2F%3EMldg%20%3D%20Mldg%20%2B%201%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EEnjoy%20Excel%3CBR%20%2F%3E%3CBR%20%2F%3ENikolino%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540959%22%20slang%3D%22en-US%22%3ERe%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540959%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F736872%22%20target%3D%22_blank%22%3E%40falloute%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20N11%20is%20a%20formula%20and%20you%20believe%20the%20calculate%20event%20will%20work%20better%20for%20you%2C%20then%20maybe%20this%20is%20closer%20to%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Calculate()%0A%20%20%20%20%20Dim%20source%20As%20Range%0A%20%20%20%20%20Dim%20lastCell%20As%20Range%0A%20%20%20%20%20%0A%20%20%20%20%20Set%20source%20%3D%20Me.Range(%22N11%22)%0A%20%20%20%20%20%0A%20%20%20%20%20With%20Worksheets(%22Sheet2%22)%0A%20%20%20%20%20%20%20%20%20%20Set%20lastCell%20%3D%20.Cells(.Rows.Count%2C%201).End(xlUp)%0A%20%20%20%20%20End%20With%0A%20%20%20%20%20%0A%20%20%20%20%20If%20lastCell.Value%20%26lt%3B%26gt%3B%20source.Value%20Or%20(IsEmpty(lastCell)%20Xor%20IsEmpty(source))%20Then%0A%20%20%20%20%20%20%20%20%20%20lastCell.Offset(1%2C%200).Value%20%3D%20source.Value%0A%20%20%20%20%20End%20If%0A%20%20%20%20%20%0A%20%20%20%20%20Set%20source%20%3D%20Nothing%0A%20%20%20%20%20Set%20lastCell%20%3D%20Nothing%0A%20%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542396%22%20slang%3D%22en-US%22%3ERe%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542396%22%20slang%3D%22en-US%22%3E%3CP%3EYeah%20so%20N11%20is%20being%20calculated%20and%20then%20being%20brought%20to%20A2%20and%20which%20is%20in%20a%20table%20and%20A2%20is%20empty.%20I%20tried%20to%20above%20code%20it%20gave%20me%20a%20Run-time%20error%20'9'%3A%20Subscript%20out%20of%20range%20then%20'debug'%20highlighted%20%22With%20Worksheets%20(%22Sheet2%22)%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542495%22%20slang%3D%22en-US%22%3ERe%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542495%22%20slang%3D%22en-US%22%3E%3CP%3EOkay%20yeah%20that%20works%20perfect!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542501%22%20slang%3D%22en-US%22%3ERe%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542501%22%20slang%3D%22en-US%22%3EI%20am%20guessing%20the%20target%20worksheet%20was%20named%20something%20other%20than%20%22sheet2%3F%22%20I'm%20glad%20to%20see%20you%20got%20it%20working.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1542607%22%20slang%3D%22en-US%22%3ERe%3A%20When%20a%20value%20changes%20it%20then%20is%20transfered%20to%20another%20sheet%20but%20also%20retains%20each%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542607%22%20slang%3D%22en-US%22%3E%3CP%3EYeah%20that%20was%20on%20my%20part%2C%20and%20I%20changed%20the%20formula%20around%20a%20bit%20with%20the%20table%20which%20immediately%20had%20it%20outputted%20in%20the%20next%20sheet%20so%20probably%20was%20a%20mix%20of%20both%20but%20not%20100%25.%20Thank%20you%20for%20the%20assistance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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.

9 Replies

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!

@JMB17 

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?

Just put it like this ... in case it should get more complicated ... although the answer from JMB17 is the quickest and easiest.
Fresh from the internet address ... with a little adjustment it could work ...


In the class module "This workbook"

Private Sub Workbook_Open()
'Start der Control-Routine
Ctrl_Each_Minute
End Sub

'In ein Modul
Public CtrlValue, Mldg As Long


Sub Control_each_Minute()
'Variablen erstellen
Dim Qe As Integer
Dim Msg As String, wks As String, myCell As String
'Prepare variables
Msg = ""
wks = "Tabelle1" ''Table in which the value is
myCell = "A1" 'Cell to be checked
'The first time the variable is started, it must be checked and filled
If IsEmpty(CtrlValue) Then
CtrlValue = Worksheets(wks).Range(myCell)
Mldg = 0
End If
'Start for the next check
Application.OnTime Now() + TimeValue("00:01:00"), "control_each_Minute"
'Message when the value has been changed
If Worksheets(wks).Range(myCell) <> CtrlValue Then
Msg = "Der Kontrollwert " & CtrlValue & " hat sich geändert." & Chr$(13)
Msg = Msg & " Would you like the new value " & Worksheets(wks).Range(myCell) & " as a control value? "
If Mldg > 0 Then
Qe = MsgBox(Msg, vbCritical + vbYesNo + vbDefaultButton1, "ACHTUNG: " & Mldg & ". ÄÄnderungsinformation")
If Qe = 6 Then
'Takeover of the new value as a control value
CtrlValue = Worksheets(wks).Range(myCell)
'Reset Counters
Mldg = 0
Exit Sub
End If
'add up the Counters
Mldg = Mldg + 1
Else
Qe = MsgBox(Msg, vbCritical + vbYesNo + vbDefaultButton1, " ATTENTION: Change information ")
If Qe = 6 Then
'Take over value as a Control value
CtrlValue = Worksheets(wks).Range(myCell)
'ResetCounters
Mldg = 0
Exit Sub
End If
'Add up Counters
Mldg = Mldg + 1
End If
End If
End Sub

Enjoy Excel

Nikolino
Best Response confirmed by falloute (New Contributor)
Solution

@falloute 

 

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.

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!

 

 

 

@JMB17