copying worksheets to a new workbook without formulae referencing original workbook

[pjdeeb]

I have a workbook I need to copy multiple worksheets from into a data
file with one existing worksheet. The copied worksheets contain
formulae referencing sheet one from the original so when they are in
the new workbook they reference sheet one of the original workbook
instead of sheet one of the new workbook like i want them to. Any
ideas on how to keep it from referencing the original?

Sheets(Array("Dot 1", "Dot 2", "Dot 3", "Dot 4", "Dot 5")).Copy
Before:=Workbooks _
("filename.dat").Sheets(2)

Original:
='Raw Data'!F29
New:
='[TVS-Wafer-Master.xls]Raw Data'!F29

 

[Guest]

Are you just talking about copying over as values, so there is no reference
to the original workbook/worksheet? If so, then once you select the new
workbook/worksheet, paste as values:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

....PC

 

[pjdeeb]

No, I meant keeping the formulae intact without the reference. After
poking around some more I found a suggestion to update the link using
the ActiveWorkBook.ChangeLink function. That solved my problem. Thank
you.