Search code examples
excelvbacopy-paste

Remember original value after running code


I'm trying to create a button that runs a fairly simple bit of VBA code in Excel, but I'm unsure how to proceed with the last bit.

Basically what my code does, is copy 3 different cells, run a calculation, then copy 3 other cells and then do the same calculation in a different cell.

After that bit of code has run, I would like to restore the values in Sheet Y B8, B9 and I3 to their original value that they were before the code was run. How do I go about this?

Sub Klima()
Sheets("X").Range("M5").Copy Destination:=Sheets("Y").Range("B8")
Sheets("X").Range("M6").Copy Destination:=Sheets("Y").Range("B9")
Sheets("X").Range("M7").Copy Destination:=Sheets("Y").Range("I3")
Range("M8").Value = Evaluate("(SUM(Y!$H$18:$H$2897)*60)/1000")
Sheets("X").Range("N5").Copy Destination:=Sheets("Y").Range("B8")
Sheets("X").Range("N6").Copy Destination:=Sheets("Y").Range("B9")
Sheets("X").Range("N7").Copy Destination:=Sheets("Y").Range("I3")
Range("N8").Value = Evaluate("(SUM(Y!$H$18:$H$2897)*60)/1000")
End Sub

Solution

  • Store your values in a variable, do your calculations and restore them.

    Option Explicit 
    
    Public Sub Remember()
        With Sheets("Y")
            'store values in variables
            Dim ValB8 As Variant
            ValB8 = .Range("B8").Value
            Dim ValB9 As Variant
            ValB9 = .Range("B9").Value
            Dim ValI3 As Variant
            ValI3 = .Range("I3").Value
        
            'do your calculation
            Klima
            
            'restore original values
            .Range("B8").Value = ValB8 
            .Range("B9").Value = ValB9 
            .Range("I3").Value = ValI3 
        End With
    End Sub