How to save workbook when cell value changes and NOT when just recalculating

[zwicky]

I am trying to write a macro to saveas a file every time a particular cells value changes I have tried the code below but it will save every time the cell recalculates even if the cell value does not change. I need it to save only when the value changes not every recalculation. please help

Private Sub Worksheet_Calculate() 
newval = Sheets("T,S,W").Range("AH1").Value 
If newval <> olval Then 
    Application.EnableEvents = False 
    olval = Sheets("T,S,W").Range("AH1").Value
    ActiveWorkbook.SaveCopyAs "path.xlsm" 
    Application.EnableEvents = True 
End If 
End Sub

 

[zwicky]

"So, on the next run, you need to read the olval before comparing to newval"
how and where would you write the code for this exactly

 

[Zot]

"So, on the next run, you need to read the olval before comparing to newval"
how and where would you write the code for this exactly

Say you store the olval in Sheets("ABC").Range "A1"), then

Private Sub Worksheet_Calculate()
olval = Sheets("ABC").Range("A1")                           ' read the stored value to compare with newval
newval = Sheets("T,S,W").Range("AH1").value          ' read newval
If newval <> olval Then                                              ' compare olval and newval and if different then save sheet
    Application.EnableEvents = False
'    olval = Sheets("T,S,W").Range("AH1").value        ' no need this. Can just write updated olval in its stored location as below
    Sheets("ABC").Range("A1") = newval                    ' write new oldval to replaced in stored location.
    ActiveWorkbook.SaveCopyAs "path.xlsm"
    Application.EnableEvents = True
End If
End Sub

 

[zwicky]

Say you store the olval in Sheets("ABC").Range "A1"), then

Private Sub Worksheet_Calculate()
olval = Sheets("ABC").Range("A1")                           ' read the stored value to compare with newval
newval = Sheets("T,S,W").Range("AH1").value          ' read newval
If newval <> olval Then                                              ' compare olval and newval and if different then save sheet
    Application.EnableEvents = False
'    olval = Sheets("T,S,W").Range("AH1").value        ' no need this. Can just write updated olval in its stored location as below
    Sheets("ABC").Range("A1") = newval                    ' write new oldval to replaced in stored location.
    ActiveWorkbook.SaveCopyAs "path.xlsm"
    Application.EnableEvents = True
End If
End Sub

how do I store olval in Sheets("ABC").Range("A1") ?

 

[zwicky]

Thanks, I got it to work with this code, I copy pasted AH1 each time into another cell (AA1) and made Olval = AA1

Private Sub Worksheet_Calculate()
newval = Sheets("T,S,W").Range("AH1").Value
Olval = Sheets("T,S,W").Range("AA1").Value
If newval <> Olval Then
    Application.EnableEvents = False
    ActiveWorkbook.SaveCopyAs "path.xlsm"
    Sheets("T,S,W").Range("AH1").Copy
    Sheets("T,S,W").Range("AA1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Application.EnableEvents = True
End If
End Sub

 

[Zot]

Thanks, I got it to work with this code, I copy pasted AH1 each time into another cell (AA1) and made Olval = AA1

Private Sub Worksheet_Calculate()
newval = Sheets("T,S,W").Range("AH1").Value
Olval = Sheets("T,S,W").Range("AA1").Value
If newval <> Olval Then
    Application.EnableEvents = False
    ActiveWorkbook.SaveCopyAs "path.xlsm"
    Sheets("T,S,W").Range("AH1").Copy
    Sheets("T,S,W").Range("AA1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Application.EnableEvents = True
End If
End Sub

Ahhh Good to hear that

 

[zwicky]

Actually no need for copy paste, I got your suggested way to work also, thanks again

Private Sub Worksheet_Calculate()
newval = Sheets("T,S,W").Range("AH1").Value
Olval = Sheets("T,S,W").Range("AA1").Value
If newval <> Olval Then
    Application.EnableEvents = False
    ActiveWorkbook.SaveCopyAs "path.xlsm"
    Sheets("T,S,W").Range("AA1") = newval
    Application.EnableEvents = True
End If

End Sub