RandBetween - prevent values changing - VBA?

[robbertly]

Hello,

My question is in relation to RandBetween and freezing the output so that it does not change each time.

I have multiple cells containing RandBetween, I would like to be able to freeze the output/values so that they do not recalculate each time the sheet / workbook opens or I do something on the sheet.

I would like to create multiple sheets with values created by RandBetween and then instruct excel to change the output to value only, ie. create a 'master' version, copy the master version to create multiple sheets and have excel freeze the output so that all sheets show value only output

Any assistance greatly appreciated.

Thanks,

 

[duggie33]

Copy the cells with the RANDBETWEEN formula and then paste special, values.

Hope that helps,

Doug

 

[robbertly]

Hi,

Thanks for the reply, Yes that worked....

Just one other question. How can I apply that to multiple sheets without having to go into each sheet and activate the macro?

Is that possible?

Thanks,

 

[duggie33]

How many sheets need this to be done? Is the data in the same cells on every worksheet in the workbook? Are the cells with the RANDBETWEEN formulas the only cells with formulas? There are a lot of ways to approach this.

 

[robbertly]

Hi,

Many thanks for taking the time to follow up on this.

Essentially this is the outline:

• The workbook has 24 sheets, and I would like to be able to apply the macro / code / VBA etc to all 24 sheets
• The cells only have RandBetween content - no formulas or other content
• I want it to apply to the same specific cells on each worksheet - B1 to Y10 and B14 to Y2060

That's it.

Any assistance will be appreciated.

Thanks

 

[duggie33]

No problem.

Try this...Ctrl-select multiple worksheets, on the visible sheet select one of the two ranges you mentioned, right-click copy, right-click paste special values. Do the same for the other range and you should be done.

I tested on two sheets, but it should work for as many sheets as you Ctrl-select.

Doug

 

[robbertly]

Hi,

Just finished checking.... yes, works perfectly

I was expecting some complicated VBA or script... just goes to show the power and flexibility of excel!

Many thanks for your assistance, you saved me invaluable time and frustration!

Keep up the good work with other excel beginners here on MrExcel

Regards,

Robbert

 

[duggie33]

You're welcome. There are many ways to achieve the same results...this one struck me as the easiest and/or most appropriate.

We are glad to help and thanks for the feedback!

Doug

 

[JEC]

You can also do something like this.
Everytime when you change the value in Cell A1, the cells are recalculating. Use this formula

=INT(MOD(A1*SEQUENCE(5,5)/PI(),1)*10^5)