Auto Copy Paste range of cells if the value in other cells change

  • I trying to figure out how to automatically copy the values and formatting from cells range (E72:U95) and paste the values and formatting only, not the formulas on cells range (E45:U68) every time value on cell (Y12) changes to any value. I use this to save last entered values on E72:U95 after they are deleted.


    Any assistance is greatly appreciated, thanks in advance!!


    I tried this code i found on the web but is not working properly after the first cell change and when it does trigger miss some values and formatting


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)  If Target.Cells.Count > 1 Then Exit Sub
       If Not Intersect(Target, Range("y12")) Is Nothing Then
        Range("E72:U95").Copy
        Range("E45:U68").PasteSpecial xlPasteValues
    End If
    End Sub
  • Re: Auto Copy Paste range of cells if the value in other cells change


    Possibly...

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("y12")) Is Nothing Then
            Application.EnableEvents = False
            Range("E72:U95").Copy
            Range("E45:U68").PasteSpecial xlPasteValues
            Application.EnableEvents = True
        End If
    End Sub

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Re: Auto Copy Paste range of cells if the value in other cells change


    Replace your Worksheet_Change Event code with these (make sure you include the "Dim x" when copy/pasting these procedures)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Auto Copy Paste range of cells if the value in other cells change


    Thanks for this dangelor, tried several times with no luck, nothing happen, no error message, no anything...any suggestions?

  • Re: Auto Copy Paste range of cells if the value in other cells change


    Thanks a lot KjBox tried several times with no luck, nothing happen either, no error message, no anything...any suggestions?


    Quote from KjBox;795811

    Replace your Worksheet_Change Event code with these (make sure you include the "Dim x" when copy/pasting these procedures)

  • Re: Auto Copy Paste range of cells if the value in other cells change


    That code works perfectly for me with a mock file I made.


    Can you attach your workbook.
    [sw]*[/sw]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Auto Copy Paste range of cells if the value in other cells change


    It is possible that while you have been trying to develop your code you ended up turning off "EnableEvents" for your file.


    Run this then try my codes again.

    Code
    Sub Enable_Events()
        Application.EnableEvents = 1
    End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Auto Copy Paste range of cells if the value in other cells change


    My code worked OK on your worksheet. Suggest you run Kj's Enable Events sub.

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Re: Auto Copy Paste range of cells if the value in other cells change


    Thanks KjBox, workbook attached, I run it and tried the code again with no luck, i also thinking there may be something disable, i tried many times different codes, then, no one want to do any,


    Quote from KjBox;795819

    It is possible that while you have been trying to develop your code you ended up turning off "EnableEvents" for your file.


    Run this then try my codes again.

    Code
    Sub Enable_Events()
        Application.EnableEvents = 1
    End Sub
  • Re: Auto Copy Paste range of cells if the value in other cells change


    Try the attached.


    Reason code was doing nothing previously was because cell Y12 was never being Selected. Also Y12 cannot be used as the Target for Worksheet_Change because it is changed by a formula, you need to go back to the cell that is referenced by that formula and use that cell as the "Target Cell". Using the ranges G17:K26, O17:S26, G33:K42 or O33:S42 as the ranges for "Target" intersect makes the codes work.


    When all 5 judges scores (for White, Green, Blue, or Yellow) are entered the "PREVIOUS STATISTICS" will get updated with the old "CURRENT STATISTICS".

  • Re: Auto Copy Paste range of cells if the value in other cells change


    Thanks dangelor, i don't know why but it does not do anything on my side, any other suggestions...? if you upload my worksheet back with your code may help?


    Quote from dangelor;795822

    My code worked OK on your worksheet. Suggest you run Kj's Enable Events sub.

  • Re: Auto Copy Paste range of cells if the value in other cells change


    Have you tried the file I attached in Post# 11?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Auto Copy Paste range of cells if the value in other cells change


    Quote from KjBox;795826

    Try the attached.


    Reason code was doing nothing previously was because cell Y12 was never being Selected. Also Y12 cannot be used as the Target for Worksheet_Change because it is changed by a formula, you need to go back to the cell that is referenced by that formula and use that cell as the "Target Cell". Using the ranges G17:K26, O17:S26, G33:K42 or O33:S42 as the ranges for "Target" intersect makes the codes work.


    When all 5 judges scores (for White, Green, Blue, or Yellow) are entered the "PREVIOUS STATISTICS" will get updated with the old "CURRENT STATISTICS".


    Thank you so much!! KjBox.... Beautifully executed..... code is doing its thing properly.... greatly appreciate your assistance.... cheers!!

  • Re: Auto Copy Paste range of cells if the value in other cells change


    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!