Here's a simple macro that changes external links to point to the
active workbook. If this is not what you were looking for, please let
me know.
I hope it helps.

Sub LinkToSelf()
Dim Awb As Workbook
Dim aLinks

Application.ScreenUpdating = False
Set Awb = ActiveWorkbook
'Get an array of the external links
aLinks = Awb.LinkSources(xlExcelLinks) 'this will return empty if
there are not external links
'As long as the array is not empty, loop through the array and change
the reference
If Not IsEmpty(aLinks) Then
For i = LBound(aLinks) To UBound(aLinks)
'error catching in case the external link cannot be changed
(if the link references a worksheet
'that exists in the linked workbook, but does not exist in the
active workbook, it will cause an
'error and the link will not be changed
On Error Resume Next
'Point the link back to the active workbook
Awb.ChangeLink Name:=aLinks(i), NewName:=Awb.FullName,
Type:=xlLinkTypeExcelLinks
On Error GoTo 0
Next i
End If
Application.ScreenUpdating = True
Erase aLinks
aLinks = Awb.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then MsgBox "There are still external links in
this workbook"
End Sub
On Feb 20, 10:16 am, -Rocket wrote:
Hey Meatshield...

I like your fix, but I was also wondering, can this be macro'd. And
moreover, can this be macro'd if the workbook is kept in different places
(possibly using the info(directory) function)?

My scenario is as follows:
My company uses a series of worksheets to prorate time spent on various
contracts. Each week, a new prorate workbook is circulated. I have created a
worksheet to be included in the workbook. When the workbook is opened by each
person, I'd like it to run the macro to update the links. The difficulty is,
that people may save their workbook in different locations, throwing off the
macro.

Any ideas?

"meatshield" wrote:
You can update the links in the workbook by using Edit-Links-Change
Source and then navigating to and selecting the new workbook(feb.xls).
I hope this helps.

On Jan 26, 10:22 am, royboy wrote:
Once a month I get a three tab worksheet of data, forumulas graphs etc. I
want to manipulate the information differently, so I created a fourth tab
(call it "my tab") with lots of formulas, formating, highlighting etc. Of
course the "my tab" worksheet has references to the other tabs as is standard
in excel: 'tab1'!B14, 'tab3'!a1*'tab1'!a2, etc.

So, lets say that I have the jan.xls spreadsheet and build mytab as
described. Now I get the feb.xls spreadsheet. I want to copy mytab to the
feb.xls. So I do edit, "move or copy worksheet", select feb.xls from the
drop down, and select create a copy.

Now "mytab" has been added to the feb.xls worksheet. All the formating etc
is there. But all of my formulas now have references to jan.xls as follows:
='[jan.xls]tab1!b14 But I don't want the reference to the old data. I
want it to look at the feb.xls spreadsheet.

Can anyone help me with how to do that?