break link to external workbook

confused in Frankfurt

Board Regular
Joined
Oct 11, 2010
Messages
53
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Oct 11, 2010
Messages
53
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
 

confused in Frankfurt

Board Regular
Joined
Oct 11, 2010
Messages
53
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
 

confused in Frankfurt

Board Regular
Joined
Oct 11, 2010
Messages
53
I had copied them over grouped - I have decided to break the links rather then change them see if that works
 

confused in Frankfurt

Board Regular
Joined
Oct 11, 2010
Messages
53
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,738
Messages
5,470,448
Members
406,700
Latest member
Mark Rob

This Week's Hot Topics

Top