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
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