Copy and paste old cell values within a range before a change occurs

[darls15]

Hi All</SPAN>

I am new to this forum and would like to ask for some assistance please.</SPAN>

I have a range of cells (B5:F5) where users can change the cells values. These changes affect values in a range of cells (H3:L32). I am trying to capture the old values (numeric and text) in the range H3:L32, prior to any change occurring and paste the data into the cell range N3:R32 as values only.</SPAN>

I have the following code which seems to work well in the copy and paste aspect; however it is copying/pasting the new values (after a change occurs) into N3:R32 instead of the old values.</SPAN>

Option Explicit</SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN>
If Target.Cells.Count > 1 Then Exit Sub</SPAN>
If Not Intersect(Target, Range("B5:F5")) Is Nothing Then</SPAN>
Range("H3:L32").Copy</SPAN>
Range("N3:R32").PasteSpecial xlPasteValues</SPAN>
End If</SPAN>
End Sub</SPAN>

I would be very appreciative if someone can please help me what I need to change here to make this work?</SPAN>

Thanks in advance</SPAN>
darls15</SPAN>

 

[HalfAce]

Hi, welcome to the board.
The reason it's copying the new values instead of the old is because the code isn't executed until the change is made.
There are a few ways I can think of to do what you're after. One would be to store the 'old' values as public variables and then call on them when the range B5:F5 gets changed.
An easier way - if it's acceptable to what you're doing - might be to use the worksheet selection change event instead of the change event.
For example, when the user selects a cell in the B5:F5 range, it "copies" (just) the values of range H3:L32 into range N3:R32, doing so before any change has been made.
If that sounds acceptable to you then you can try using this instead of the worksheet change routine you're using now.

[/COLOR]Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B5:F5")) Is Nothing Then _
    Range("N3:R32").Value = Range("H3:L32").Value

End Sub[COLOR=#333333]

Hope it helps.

 

[darls15]

Hi HalfAce</SPAN>

Thank you for your quick response. I've tried your suggestion in my spreadsheet and I think I still need further assistance as the results aren't copying/pasting as I need. I'll try to explain further.</SPAN>

An important thing I forget to mention and may be of help is that cells B5:F5 contain dropdown lists and a cell the user can type into. To be more specific, B5 is a drop down list, D5 value is entered by the user and F5 is also is a drop down list (C5 and E5 are just blanks).</SPAN>

So basically, if B5 currently displays 20 and a user selects say 30 from the list, before the number changes to 30 and affects cells H2:L32, I would like the old values for the option 20 to be copied to N3:R32.</SPAN>

I hope this makes sense. </SPAN>

Thanks again for your assistance</SPAN>
darls15</SPAN>

 

[HalfAce]

What do you mean by "drop down lists"?
I just checked this out using data validation drop downs in cells B5 & F5 and the selection change code copied over when any cells - including the drop down cells - gets selected, before anything can be changed.
Are you using something different for your drop downs? (If so, can data validation be used instead?)
If not, then what exactly are you using?

 

[darls15]

Hi again HalfAce
Thank you and my apologies. I've just retested this and you are perfectly correct, it is working as it should.
I appreciate your help with this. I'm only new to code and this has been driving me crazy trying to make it work.
darls15

 

[HalfAce]

You're most welcome.
No apologies, we were all new at coding at some point. You'll be amazed at what you'll figure out you can do with it.
I, myself, have figured out how to make it go get me a beer! (Just haven't yet found how to make it open it for me...)

Glad it helped & you're back under way.