TM1 Forum

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.

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?

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

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?

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.

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.

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,