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

zwicky

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
"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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
966
Office Version
  1. 2016
Platform
  1. Windows
"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

VBA Code:
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

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Say you store the olval in Sheets("ABC").Range "A1"), then

VBA Code:
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

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thanks, I got it to work with this code, I copy pasted AH1 each time into another cell (AA1) and made Olval = AA1
VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
966
Office Version
  1. 2016
Platform
  1. Windows
Thanks, I got it to work with this code, I copy pasted AH1 each time into another cell (AA1) and made Olval = AA1
VBA Code:
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

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Actually no need for copy paste, I got your suggested way to work also, thanks again
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,117
Messages
5,622,827
Members
415,934
Latest member
adstocking

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top