VBA Copy Cell value before Change event into different worksheet

Question

I have "Sheet10" where cell "F3" does some calculation and value changes depending on user input. User Input is on "Sheet10" cell "G6". I would like to copy the final calculated value of "F3" before the new value in changed into "Sheet12" Cell "Q3" Below is my code but I'm not sure where i went wrong. Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("F3").Address Then

        Worksheets("Sheet12").Range("Q3") = Target.Value

    End If

End Sub

@newguy I looked at the thread and i still could not get it to work — Leocodetwist, Jun 29 '16 at 15:30

Scott Holtzman · Accepted Answer · 2016-06-29T19:03:39.583

1

This question has been asked on SO before, but I am too lazy at the moment to search for it ...

Since the Worksheet_Change events fires after a cell changes, the Target.Value will reflect the new value. However, the Undo command can be used to get the old value by storing the new value into a variable, running the undo, then updating the target with the new value and storing the old value in the desired place.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("G6").Address Then

        Application.EnableEvents = False

        Dim sOldValue As String, sNewValue As String
        sNewValue = Target.Value

        Application.Undo
        sOldValue = Range("F3").Value

        Target.Value = sNewValue

        Worksheets("Sheet12").Range("Q3") = sOldValue

        Application.EnableEvents = True

    End If

End Sub

For multiple cells do this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("G6").Address Then

        Application.EnableEvents = False

        Dim sOldValue As String, sNewValue As String
        sNewValue = Target.Value

        Application.Undo

        Dim rOld as Range
        Set rOld = Range("F3:V3").Value

        Target.Value = sNewValue

        Range("V8:AD8").Value = rOld.Value

        Application.EnableEvents = True

    End If

End Sub

edited Jun 29 '16 at 19:03

answered Jun 29 '16 at 15:19

Scott Holtzman

27,099
5
37
72

I tried this but value still does not show up on sheet12 – Leocodetwist Jun 29 '16 at 15:29
@Leofig123 did you place the code inside the sheet module for sheet10? I tested the code and it works perfectly – Scott Holtzman Jun 29 '16 at 15:46
i inserted the code into sheet module sheet 10 and nothing. I inserted the code in the module and the systems keeps looping. The cell ("G6") where user inputs a value changed to $F$3 and is doing some funky things and cell "F3" where the change happens is changed to 0 and cell Q3 is blank. I updated my question hopefully – Leocodetwist Jun 29 '16 at 15:58
1

@Leofig123 - okay. that's a different story than what you originally wrote. see my modified code. you may need to do a calc after the undo if you have calcs set to manual, but otherwise, it should work. It will place the old `F3` before the change in `G6` into `Q3` in `Sheet12`. – Scott Holtzman Jun 29 '16 at 16:08
Unfortunately nothing is showing on sheet 12 – Leocodetwist Jun 29 '16 at 16:19
So here is what cell F3 contains... F3 links to another cell in another worksheet(3) which sum up values from different areas in worksheet(3) dependent on userinput in sheet10 cell G6. So i would like to copy the summed value to Q3 before the value changes again – Leocodetwist Jun 29 '16 at 16:25
Is your sheet actually named `Sheet12`? If so, place a break on the `If Target.Address ... ` line, make a change to `G6` on Sheet10 and step through the code line-by-line, making note of variable values and such, and then you can find what the issue is. Impossible for me to test without your actual file. – Scott Holtzman Jun 29 '16 at 16:37
So i got it to work all in one page . how would i do this for multiple cells. is the below correct? ' sOldValue = Range("F3:N3").Value Target.Value = sNewValue Range("V8:AD") = sOldValue Application.EnableEvents = True' – Leocodetwist Jun 29 '16 at 18:47
1

@Leofig123 - you are really asking a new question. Next time be more specific with your needs so it's all upfront. This time I have made an edit to assist you in your additional question that is out-of-scope from the first question, but in the future, please keep questions pointed and specific. It's more useful to others that way. – Scott Holtzman Jun 29 '16 at 19:04
1

Thank you i appreciate all your help – Leocodetwist Jun 30 '16 at 01:37
Too lazy to do a search but then writes full response... – ProfessorFluffy Aug 03 '17 at 19:19
@ProfessorFluffy - Too lazy to search = I had that code *right at my fingertips*, so it was much easier to answer than dig for other answers. – Scott Holtzman Aug 03 '17 at 21:30
1

I know. I just thought it was funny. – ProfessorFluffy Aug 03 '17 at 21:39

VBA Copy Cell value before Change event into different worksheet

1 Answers1