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

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

"(E-Mail Removed)" wrote:

> 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
>
>

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.

Paige wrote:
> 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