Deactivate then Reactivate UpdateLinks
 

I have a worksheet which I have linked to several other workbooks (20+). When
opening the file, it takes 20 secs or so to update all the links at once
which is acceptable.

I am using VBA to edit each cell's formula in order to read from one set of
linked tabs to another. (see example)

A = Range("D5")
B = Range("H3")
C = A & "'!"
D = B & "'!"

Range("D8:V50").Select
Selection.Replace What:=C, Replacement:=D, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("D5").Select

This works fine when there are just a few cells, however since the link is
updated as each cell is edited, the more cells = more time to update. Right
now it can take 15 min or more to update.

Question - can the updating of the links be 'turned off' so the edit and
replace can take place, then restored allowing for the cells to update all at
once simular to when the file is first updated at open?

Thanks in advance for any tips...

- -TB- -

Deactivate then Reactivate UpdateLinks
 

Have you tried setting calculation to manual

Just a thought. I don't know if it will help or not.

--
Regards,
Tom Ogilvy



"TOMB" wrote in message
...
I have a worksheet which I have linked to several other workbooks (20+).
When
opening the file, it takes 20 secs or so to update all the links at once
which is acceptable.

I am using VBA to edit each cell's formula in order to read from one set
of
linked tabs to another. (see example)

A = Range("D5")
B = Range("H3")
C = A & "'!"
D = B & "'!"

Range("D8:V50").Select
Selection.Replace What:=C, Replacement:=D, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("D5").Select

This works fine when there are just a few cells, however since the link is
updated as each cell is edited, the more cells = more time to update.
Right
now it can take 15 min or more to update.

Question - can the updating of the links be 'turned off' so the edit and
replace can take place, then restored allowing for the cells to update all
at
once simular to when the file is first updated at open?

Thanks in advance for any tips...

- -TB- -

Deactivate then Reactivate UpdateLinks
 

Thanks for the reply and yes I tried that, both in tools/options and by
placing it in the proceedure. Also, screenupdating=false do not result in
reduced time to run.


"Tom Ogilvy" wrote:

Have you tried setting calculation to manual

Just a thought. I don't know if it will help or not.

--
Regards,
Tom Ogilvy



"TOMB" wrote in message
...
I have a worksheet which I have linked to several other workbooks (20+).
When
opening the file, it takes 20 secs or so to update all the links at once
which is acceptable.

I am using VBA to edit each cell's formula in order to read from one set
of
linked tabs to another. (see example)

A = Range("D5")
B = Range("H3")
C = A & "'!"
D = B & "'!"

Range("D8:V50").Select
Selection.Replace What:=C, Replacement:=D, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("D5").Select

This works fine when there are just a few cells, however since the link is
updated as each cell is edited, the more cells = more time to update.
Right
now it can take 15 min or more to update.

Question - can the updating of the links be 'turned off' so the edit and
replace can take place, then restored allowing for the cells to update all
at
once simular to when the file is first updated at open?

Thanks in advance for any tips...

- -TB- -