Update values/Edit > Links problem

[padraigb]

Hi i am looking for a solution to an updating links problem.

I have a large spreadsheet with approx 30 sheets (There is one for each company department).

They link to individual workbooks where the detailed calculations are carried out (also one for each department)

In some cases we set up a new department.

I create a new individual workbook.

On the large spreadsheet i create a sheet for this department.

I copy across the formulas from another departments sheet

But i then need to link the formulas to the new departments individual workbook.


My issue is that if i go

1. Edit > Links and change the source i change this source on all workbooks.

I only want to change the source on the active sheet.

2. If i go Edit >Find and Replace i then get a 'update values' prompt for every single cell which is extremely time consuming to go through!


Does anyone how if

for option 1. Edit > Links There is a macro to change the links on the active worksheet only?

or for option 2. How to stop the 'update values' prompt if i use Edit > Find and Replace

Thanks for your help.

Please let me know if anything is unclear on this

pad

[Cheeky Charlie]

Try
Tools->Options->Edit
Untick 'Ask to update automatic links'

hth

[padraigb]

Hi Charlie,

Unfortunately does not seem to work for me. When i go Edit> Find and Replace same message comes up.

pad

[Cheeky Charlie]

How about closing the workbooks you don't want to change before doing this:

1. Edit > Links and change the source i change this source on all workbooks.

?

[Marc2010]

No, its not possible. All you can do is using the search and replace function...

Regards
Marc

[Cheeky Charlie]

Perhaps you could put the find & replace in code and turn off displayalerts

Like this:

Please Login or Register  to view this content.