break link to external workbook

[confused in Frankfurt]

Hi,

I have the following problem:
I am copying sheets from one workbook into another it has formulas linked to a sheet in the source workbook. I want to relink these to the new workbook and had the following formula which is creating an error 438.



ActiveWorkbook.ChangeLink Name:="Y:\pub\2011\Forecasting\Revenue Forecasting\CreationFile\" & _
"SamsUPdatecheckv2.xls", newname:=(ActiveWorkbook), _
Type:=xlLinkTypeExcelLinks

 

[Andrew Poulsom]

It would be:

newname:=ActiveWorkbook.FullName

but it must be saved.

If you group the sheets and copy them the formulas should remain relative provided that the referenced sheet is also copied.

 

[confused in Frankfurt]

Thanks Andrew,

This worked but I now have an error 1004

I can't copy all the sheets from the source workbook in one go. I copy two sheets in a loop and then in a later step a third sheet which is the one still linking to the source sheet. Manually I just change the source of the links in the links section

what am I doing wrong ?

Regards
Sarah

 

[Andrew Poulsom]

Why can't you copy all the sheets at once?

 

[confused in Frankfurt]

I have one master file where I create country specific lists which I then split out to a separate file for the receiving country manager

 

[Andrew Poulsom]

Can't you group the sheets before copying?

http://office.microsoft.com/en-us/excel-help/select-one-or-multiple-worksheets-HP005202492.aspx

The macro recorder will give you the VBA syntax.

 

[confused in Frankfurt]

I had copied them over grouped - I have decided to break the links rather then change them see if that works

 

[Andrew Poulsom]

Do you still have links?

 

[confused in Frankfurt]

no longer - the last info's which were linked are now hard numbers and no longer linked to the source sheet, thanks for your help