How can I make excel recalculate whole workbook when change made to one cell?

[ndtsteve]

Hi people,
can someone tell me how to force excel to recalculate a whole workbook (not huge, just 10 sheets) when I make a change on one of the sheets. Right now, when I change something I then have to go through each page and click somewhere in each page to get it to 'refresh' the calculations. Some of the entries are done thru vba some of them are pastelinked.

Thanks in advance

[kenny.fsw]

Hiya mate,

Go to the formulas tab and click calculate now. Cant remember if you will need to click the first sheet then shift click the last to highlight them all. If there are data tables in there, go to the data tab and click refresh all.

[ndtsteve]

thanks, but I was hoping that someone may know a way to do this as soon as the value in a field was changed, i.e a bit of vba code I can stick in each sheet or something.

[ndtsteve]

Anyone have any help or ideas, wanted to try put this to bed before the weekend??

[JosephP]

I'm not sure why you need to do this-do you have calculation set to manual? if not, every linked calculation should happen automatically.

[ndtsteve]

Workbook is set to automatic calculation. Paste linked stuff updates automatically, but appears to me that, for example, a certain cell has 'yes' in it, there is some coding saying that if this cell is yes then someting on another sheet changes its value. This value does not change until I go to that sheet and click on a/any cell, soon as I click in that sheet then the change takes effect.
Sorry if I am not explaining myself very well, but I'm new to this and don't know the 'technical terms'

[ndtsteve]

Any takers?

I have found "Application.CalculateFull" where do I put this, in the first sheet or every sheet?

[JosephP]

you'd put it in whatever event you want to trigger it - eg a worksheet_change if you want one sheet to control it, or thisworkbook_sheetchange if you want all sheets to trigger it. but it sounds to me like you ought to be fixing the other code instead - this really shouldn't be necessary.

[ndtsteve]

Please Login or Register  to view this content.

This is the coding in my first sheet, only 4 of the 10 sheets have any coding.

where in this coding would your suggestion go?
What I want is that any change in any of the 10 sheets makes the whole worksheet update.

Sorry, but I am very new to vba and still groping my way around

[JosephP]

can I ask why you are using a selection change event rather than a change event? is the value in AM15 entered manually or the result of a formula?

[ndtsteve]

Joseph, AM15 is a manual entry. I'm using a selection change event because this is something I have seen in another link or google. I am trying to learn this by help menus, forum links etc so all I know I have seen somewhere else. Maybe it's not the best way but it's the way I have done it

[JosephP]

I would suggest a change event instead:

Please Login or Register  to view this content.

[ndtsteve]

Thanks Joseph, but still doesn't recalculate the whole workbook.
Have attached file for you to have a look at.
Example: entering data in red cells on first sheet should update field in tool refurb sheet but I still have to click in the marked cells on refurb sheet for changes to take effect.
There are also fileds on other sheet where I have to do the same thing.
Any pointers or suggestions would be greatly appreciated.

[JosephP]

oh my. do you have any objection to reworking this to use formulas instead of code?

[ndtsteve]

No Objections.....just not the knowledge (see PM to you)

[JosephP]

ok-I'll take another look when my clients give me some peace, jet lag permitting.