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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
601
Office Version
  1. 2016
Platform
  1. Windows
In the code, you are reading the value in range AH1 as newval. Later you read the same location as olval. Where did you store your actual olval?

I would use change event in Sheet("T,S,W")

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AH1")) Is Nothing Then
    If Target <> olval Then
        Application.EnableEvents = False
        olval = Target
        ActiveWorkbook.SaveCopyAs "path.xlsm"
        Application.EnableEvents = True
    End If
End If

End Sub

This will trigger each time any change  in range AH1.
 

zwicky

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I dont think Change event sheet will work as value of ah1 changes via formula, there is no manual entries in sheet, its all formulas and data stream.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
601
Office Version
  1. 2016
Platform
  1. Windows
I dont think Change event sheet will work as value of ah1 changes via formula, there is no manual entries in sheet, its all formulas and data stream.
Ahh... you are right if change during runtime
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
601
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

So, you have another macro that hen executed will change value in AH1? Why not just execute save if the value changed at the end of routine?
 

zwicky

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Cell Ah1 contains a simple countif formula =countif(ah2:ah2000>0)
Range ah2:ah2000 also contain formulas which have datastream precedents
 

zwicky

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Everytime AH1 ticks up I need a copy of the sheet saved
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
601
Office Version
  1. 2016
Platform
  1. Windows
Your Worksheet calculate event should work I believe. The only thing I see is that once the save is triggered you reread newval as olval, but I don;t know where you saved this olval. Otherwise you always compare with same value or empty olval. I just don't see where you read olval from. Maybe I was confused :)
 

zwicky

New Member
Joined
Dec 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Your Worksheet calculate event should work I believe. The only thing I see is that once the save is triggered you reread newval as olval, but I don;t know where you saved this olval. Otherwise you always compare with same value or empty olval. I just don't see where you read olval from. Maybe I was confused :)
I think you are on the right track,
The issue may lie with olval,
As the code does what it is supposed to do on the very first change in value of ah1, however after this it saves everytime there is a change in the precedents of ah2:ah2000 which do not necessarily change the value of ah1,
Any suggestions on what to do with olval
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
601
Office Version
  1. 2016
Platform
  1. Windows
Before compare you should read what is the olval, but I did not see that. You must have saved olval somewhere.

You read the newval with newval = Sheets("T,S,W").Range("AH1").Value. It is in a cell location. Once you compare and if it is different from olval (which you have to read to compare), you save the worksheet. Then you need to replace the olval with newval. In order to compare with newval in future, you need to save it in a location in worksheet, right? I did not see that like Sheets("ABC").range("A1")=olval.... something like that.

So, on the next run, you need to read the olval before comparing to newval.... like olval=Sheets("ABC").range("A1")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

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