doent recalculate on save

[gilligans]

Hi All,
I have this workbook that has around 12-15 worksheets.
At present there is a formula gets() that takes a arugument called
"Calc".
now it is set to automatic recalculate in the prefereneces.
so whenever i change a sheet i have to then go to another sheet to
where the calc field resides and just change the number to any number
other then zero and immediately it calculates all the sheets in the
workbook.

So now,I want to recalculate only one sheet at a time instead of all
the sheets and also dont want to depend on the calc field of another
sheet.
So i made the preferences to manual and checked the box that says
'recalculte on save'.
and also in the function gets () im replacling the calc to '6'.
And for each sheet in vba- i wrote:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Calculate
End Sub

But when i do alt+s to save it just saves but does not recalculate.

how do i make it recalculate when it saves..

Please help.
Thanks,
gilligans

 

[NickHK]

You could use the _BeforeSave event to run Application.Calculate.
This will calculate all open WBs.
Otherwise you can call the .calculate method of each WS in ThisWorkbook

NickHK

 

[gilligans]

Thanks Nick,
But I am new to excel and vba. can u be little bit more specific..
I did write the following code for each sheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Calculate
End Sub

Is this right, because it is not helping...
its not recalculating..
Please correct me..


Thanks for the help.
Jyothi

 

[NickHK]

So no formulae on your worksheet or anywhere in the workbook are being
recalculated ?
Even after calling .Calculate ?

NickHK

 

[gilligans]

yes, thats true.
after i change a particular cell, then another cell needs to be update,
cause that has the formula.
So then i do a save.
It just saves . no recalculation done..

gilligans
help