View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
TOMB TOMB is offline
external usenet poster
 
Posts: 25
Default 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- -