Slow file with INDIRECT.EXT

[KateB]

I have downloaded and successfully used the indirect.ext function. However,
the file is linking to 16 other files currently, and this will expand as the
year goes on! I have set the volatile argument to false, but each time I add
another file to be linked to, when it pulls the new info it goes through the
process of checking all the other cells (160 cells for each linked file, so
currently approx 2,500!)

Is there any way I can stop it doing that, and only update the results if I
ask it to? It currently takes about a minute per file and I can't afford for
my computer to be frozen for that long. If not, then I will have to
copy/paste values but that kind of spoils the reason for using the indirect
function in the first place.

Many thanks

Kate

 

[ryguy7272]

Slow Now; Speed UP:
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
.... ... ...
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Macro specific to one sheet in a workbook:
sheets("Sheet1").activate
Range("A1:Z100").Select
This will take you to the desired sheet for the range selected.

If you code it like this...
sheets("Sheet1").range("A1:Z100").select
You will get an error - script out of range - if your are not on the
activesheet.