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


--
gilligans
------------------------------------------------------------------------
gilligans's Profile: http://www.excelforum.com/member.php...o&userid=35026
View this thread: http://www.excelforum.com/showthread...hreadid=567193

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" wrote
in message ...

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


--
gilligans
------------------------------------------------------------------------
gilligans's Profile:
http://www.excelforum.com/member.php...o&userid=35026
View this thread: http://www.excelforum.com/showthread...hreadid=567193

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


--
gilligans
------------------------------------------------------------------------
gilligans's Profile: http://www.excelforum.com/member.php...o&userid=35026
View this thread: http://www.excelforum.com/showthread...hreadid=567193

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

NickHK

"gilligans" wrote
in message ...

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


--
gilligans
------------------------------------------------------------------------
gilligans's Profile:
http://www.excelforum.com/member.php...o&userid=35026
View this thread: http://www.excelforum.com/showthread...hreadid=567193

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



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

NickHK


--
gilligans
------------------------------------------------------------------------
gilligans's Profile: http://www.excelforum.com/member.php...o&userid=35026
View this thread: http://www.excelforum.com/showthread...hreadid=567193