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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Cell Ah1 contains a simple countif formula =countif(ah2:ah2000>0)
Range ah2:ah2000 also contain formulas which have datastream precedents
 
Upvote 0
Everytime AH1 ticks up I need a copy of the sheet saved
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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