Copying values from formulas without deleting the formulas

[Diaxus]

I'm trying to make a clickbutton that takes values and copies them into next door cells, but the macro is deleting the formula, rendering nothing to copy.

As for the 13 Dims and Sets, Range wouldn't accept multiple ranges. If there as a better way I'd appreciate the knowledge.

Sub Savelist_Click()

Dim CL1 As Range
Dim CL2 As Range
Dim CL3 As Range
Dim CL4 As Range
Dim CL5 As Range
Dim CL6 As Range
Dim CL7 As Range
Dim CL8 As Range
Dim CL9 As Range
Dim CL10 As Range
Dim CL11 As Range
Dim CL12 As Range
Dim CL13 As Range
Dim SL1 As Range
Dim SL2 As Range
Dim SL3 As Range
Dim SL4 As Range
Dim SL5 As Range
Dim SL6 As Range
Dim SL7 As Range
Dim SL8 As Range
Dim SL9 As Range
Dim SL10 As Range
Dim SL11 As Range
Dim SL12 As Range
Dim SL13 As Range
Dim DL1 As Range
Dim DL2 As Range
Dim DL3 As Range
Dim DL4 As Range
Dim DL5 As Range
Dim DL6 As Range
Dim DL7 As Range
Dim DL8 As Range
Dim DL9 As Range
Dim DL10 As Range
Dim DL11 As Range
Dim DL12 As Range
Dim DL13 As Range


Set CL1 = Range("E3:E5")
Set CL2 = Range("E8:E11")
Set CL3 = Range("E14:E21")
Set CL4 = Range("E24:E30")
Set CL5 = Range("E33:E39")
Set CL6 = Range("E42:E49")
Set CL7 = Range("E52:E61")
Set CL8 = Range("M3:M8")
Set CL9 = Range("M11:M19")
Set CL10 = Range("M22:M30")
Set CL11 = Range("M33:M41")
Set CL12 = Range("M44:M52")
Set CL13 = Range("M55:M63")
Set SL1 = Range("D3:D5")
Set SL2 = Range("D8:D11")
Set SL3 = Range("D14:D21")
Set SL4 = Range("D24:D30")
Set SL5 = Range("D33:D39")
Set SL6 = Range("D42:D49")
Set SL7 = Range("D52:D61")
Set SL8 = Range("L3:L8")
Set SL9 = Range("L11:L19")
Set SL10 = Range("L22:L30")
Set SL11 = Range("L33:L41")
Set SL12 = Range("L44:L52")
Set SL13 = Range("L55:L63")
Set DL1 = Range("C3:C5")
Set DL2 = Range("C8:E11")
Set DL3 = Range("C14:C21")
Set DL4 = Range("C24:C30")
Set DL5 = Range("C33:C39")
Set DL6 = Range("C42:C49")
Set DL7 = Range("C52:C61")
Set DL8 = Range("K3:K8")
Set DL9 = Range("K11:K19")
Set DL10 = Range("K22:K30")
Set DL11 = Range("K33:M41")
Set DL12 = Range("K44:K52")
Set DL13 = Range("K55:K63")



answer = MsgBox("This confirms runs as saved, Continue?", vbYesNo, "Harvest Update")

If answer = vbYes Then

    CL1.Value = SL1.Value
    CL2.Value = SL2.Value
    CL3.Value = SL3.Value
    CL4.Value = SL4.Value
    CL5.Value = SL5.Value
    CL6.Value = SL6.Value
    CL7.Value = SL7.Value
    CL8.Value = SL8.Value
    CL9.Value = SL9.Value
    CL10.Value = SL10.Value
    CL11.Value = SL11.Value
    CL12.Value = SL12.Value
    CL13.Value = SL13.Value
    DL1.ClearContents
    DL2.ClearContents
    DL3.ClearContents
    DL4.ClearContents
    DL5.ClearContents
    DL6.ClearContents
    DL7.ClearContents
    DL8.ClearContents
    DL9.ClearContents
    DL10.ClearContents
    DL11.ClearContents
    DL12.ClearContents
    DL13.ClearContents
   

Else

answer = MsgBox("Nothing has been changed.", vbOKOnly, "Harvest Update")

End If


End Sub

 

[Diaxus]

That may be a pain to try to decipher. Essentially, I am trying to copy the value (CL, short for Copylist) into the SL (Savelist) cell, then delete (DL, Deletelist) from a third cell that feeds the formula but does not itself contain a formula.

Something like this; Copy E3 into D3, then delete C3.

The formula in cell E3 is a sum formula which uses the data in C3, and the idea is to auto write the final answer into D3. Finally, this must be done in every range listed. I need to do this without deleting the formula in E3.

Thank you for your time.

 

[Fluff]

How about

Sub Diaxus()
  Dim Rng As Range
  
   For Each Rng In Range("D3:D5,D8:D11,D14:D21,D24:D30,D33:D39,D42:D49,D52:D61,L3:L8,L11:L19,L22:L30,L33:L41,L44:L52,L55:L63")
      Rng.Offset(, 1).Value = Rng.Value
      Rng.Offset(, -1).ClearContents
   Next Rng
End Sub

 

[Diaxus]

That is definitely more elegant, but it's still deleting my formulas. It seems using =.values in any variation still deletes them.

Edit: The formula is in the E3 and equivalent cells. I thought .value should only deal with the value, but maybe not?

 

[Fluff]

Ok, I had not seen post#2 when I replied, so I was going by your original code which is the wrong way round, you were copying col D into col E not vice versa.
Try

Sub Diaxus()
  Dim Rng As Range
  
   For Each Rng In Range("D3:D5,D8:D11,D14:D21,D24:D30,D33:D39,D42:D49,D52:D61,L3:L8,L11:L19,L22:L30,L33:L41,L44:L52,L55:L63")
      Rng.Value = Rng.Offset(, 1).Value
      Rng.Offset(, -1).ClearContents
   Next Rng
End Sub

 

[Diaxus]

I see, I had the sides swapped! Can't believe I didn't think to check that... Anyway, it works perfectly, thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.