Recalculate specific cells ather than whole Worksheet

[DivyaJainDJ]

Hi,

I am looking for a way to recalculate say 500 cells in TM1 worksheet (slices) rather than whole worksheet. Also, F2+Enter works only on 1 cell. Doing this 500 times is not feasible. Any suggestions?

[gtonkin]

Not sure of your exact application but I have a macro bound to Alt+R that executes Selection.Calculate
Highlight a range, press Alt+R and it gets updated.

[DivyaJainDJ]

Is it something that can be inbuilt in the excel addin itself which can be used by every employee of the company rather than just a adhoc Macro file. Something in TM1 config files or so?

[gtonkin]

I use it separately. If you want to build it in, you would need to add the code to each template and Macro-enable your templates (save as .xlsm).

If you are using TM1 Web, macros are not supported.

[DivyaJainDJ]

I am using TM1 perspectives not web. files are already heavy that is why need a solution that it calculates only specific cell, don't you think macro enabled files (xlsm) would make it more heavy?

Also, in the meantime, are you able to provide the vba script for it please?

[gtonkin]

Macro-enabling should not add more overhead unless you intend to add a lot of macro code-even then, this should not impact size too much.
Add a module to your workbook first. The code is simple:

Code: Select all

Sub CalculateSelection()
Selection.Calculate
End Sub

Bind a short-cut key to the macro after you have created it.

[DivyaJainDJ]

Thankyou so much.

[Wim Gielis]

Macro-enabling should not add more overhead unless you intend to add a lot of macro code-even then, this should not impact size too much.
Add a module to your workbook first. The code is simple:

Code: Select all

Sub CalculateSelection()
Selection.Calculate
End Sub

Bind a short-cut key to the macro after you have created it.

This is how it started for me in VBA and a couple of years later you have an add-in (xlam file) with 18,000 lines of coding
To automate Excel and TM1 tasks. Icons in the ribbon were added, but you could also use the QAT (Quick Access Toolbar) if you only have a handful of macros. Or you could create 1 macro, put it in the QAT, and pressing the icon would show a userform that lists the available macros/functionality that you or the user can pick from and launch from there. Shortcut keys are quick and useful too and don't require an icon in ribbon or QAT.
Just to show that automation can bring us many benefits, if we are prepared to code the whole thing (and sometimes use Google for code).