How to recal multiple sheets at once?

macsir · Post by **macsir** » Mon Jul 02, 2012 1:35 am

Is there any way to recal multiple sheets in one workbook at once? It would save lots of time instead of refreshing them one by one.

Alan Kirk · Post by **Alan Kirk** » Mon Jul 02, 2012 1:41 am

macsir wrote:Is there any way to recal multiple sheets in one workbook at once? It would save lots of time instead of refreshing them one by one.

I don't follow the question. If by "recal" you mean "recalculate"... which part of the [F9] (calculate all) key is not working?

Wim Gielis · Post by **Wim Gielis** » Mon Jul 02, 2012 8:02 am

Hello macsir

Either you need a short macro to do it, either you can use [F9] BUT:

- [F9] also calculates all other worksheets in all other open workbooks
- you have to set the EnableCalculation property to False, for those sheets that you do not want to be calculated:

For example with VBA: sheet1.EnableCalculation = False
Manually: go to the Properties pane in VBE and locate the same property where you can set it manually
The problem is, that this setting is not retained when you close and reopen the workbook again.
Meaning that the best approach would be to set the EnableCalculation to False in the Workbook_Open event of the workbook (to avoid manual work).

So in practice, AFAIK it will always be a macro (or otherwise an icon in the toolbar that does the same).

Wim

macsir · Post by **macsir** » Wed Jul 04, 2012 11:55 pm

Thanks, everybody. Sorry for late update.
I am using Active form excel file which has many active form reports in it. If I press F9, it would just recal values in formulas in excel itself and would not get updated data from cube, right ? I have to use "rebuild current book" to get all updated data from cube for all active form reports?

Alan Kirk · Post by **Alan Kirk** » Thu Jul 05, 2012 12:00 am

macsir wrote:Thanks, everybody. Sorry for late update.
I am using Active form excel file which has many active form reports in it. If I press F9, it would just recal values in formulas in excel itself and would not get updated data from cube, right ? I have to use "rebuild current book" to get all updated data from cube for all active form reports?

You might find the post by Summerbrewgal in this thread useful with regard to recalculating active forms.

macsir · Post by **macsir** » Thu Jul 05, 2012 1:27 am

Thanks, Alan. It is useful.

Alt-F9 = rebuild of the active form (there is also a rebuild button on the toolbar). You need to do this if you've changed any of the parameters that would affect the view generation (anything in the TM1RPTView, TM1RPTRow or added new columns with a formula linked to the TM1RPTview statement). The active form will start back in the opening state.

F9 = standard recalc (recalc button on toolbar) ... will fetch new data in the form, show/hide rows that should/should not be there based on zero suppression if you've changed a title dimension element, but doesn't reset the form (i.e. if you've drilled for instance, it will remain in that state)

Do you know what is the scenario which I need to press Shift-F9 or Ctrl-Alt-Shift F9? I am still confusing with these two.

Olivier · Post by **Olivier** » Fri Jul 06, 2012 5:41 am

SHIFT + 9 will recalculate the active spreadsheet only ( but not rebuild the active forms).

CTRL + ALT + SHIFT + F9 will "relink" the workbook to Tm1 when the connection was "confused" between Excel and "Perspectives".
i.e. SHift + F9 does not seems to refresh the data anymore where everything seems to be fine. Try then CTRL + ALT + SHIFT + F9, it shoudl then recalculate.

I am not sure what can cause the Excel / Perspectives confusion.

Hope this helps,

TM1 Forum

How to recal multiple sheets at once?

How to recal multiple sheets at once?

Re: How to recal multiple sheets at once?

Re: How to recal multiple sheets at once?

Re: How to recal multiple sheets at once?

Re: How to recal multiple sheets at once?

Re: How to recal multiple sheets at once?

Re: How to recal multiple sheets at once?