Sheets.Copy breaking the links

[PO]

Excel 2003, sp2

I use the following code to import a sheet from another workbook:
Workbooks("Data").Sheets("SiteData2").Copy
After:=ThisWorkbook.Sheets("SiteData1")

Both workbooks contain a sheet named "SiteInfo" and all SiteData sheets
contain linked formulas to the SiteInfo sheet. After the copy "SiteData2"
links to the workbook from which it originated. I however want the links to
point to SiteInfo in the workbook it was imported to.

So far I have solved this problem by looping through the SiteData2 sheet,
replacing the =[Data.xls] part of the link with "" in all cells. The
SiteData sheet contains however a lot of links and sometimes my macro
"imports" 10 or more sheets in a row. Thus the code has to loop through a
lot of cells in many sheets which makes the import take up to 30 secs.

Is there a better way to do this?

Regards
Pete

 

[Dave Peterson]

There are at least a couple of other ways. I didn't test to see if they were
any faster.

#1. After you copy the worksheet into the new workbook, you could use the
equivalent of Edit|Links and change source (if you don't have any other links
that point back to that original workbook!).

#2. Before you copy the sheet, change all the formulas to text.

Select all the cells
edit|replace
what: = (equal sign)
with: $$$$$=
replace all

Then do the copy

And then do the edit|replace in reverse -- in both the original worksheet and
the new worksheet.

(record a macro when you do it manually and you'll have the code.)

Excel 2003, sp2

I use the following code to import a sheet from another workbook:
Workbooks("Data").Sheets("SiteData2").Copy
After:=ThisWorkbook.Sheets("SiteData1")

Both workbooks contain a sheet named "SiteInfo" and all SiteData sheets
contain linked formulas to the SiteInfo sheet. After the copy "SiteData2"
links to the workbook from which it originated. I however want the links to
point to SiteInfo in the workbook it was imported to.

So far I have solved this problem by looping through the SiteData2 sheet,
replacing the =[Data.xls] part of the link with "" in all cells. The
SiteData sheet contains however a lot of links and sometimes my macro
"imports" 10 or more sheets in a row. Thus the code has to loop through a
lot of cells in many sheets which makes the import take up to 30 secs.

Is there a better way to do this?

Regards
Pete