copy worksheet without reference to old .xls file

[Guest]

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?

 

[meatshield]

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.

 

[Dave Peterson]

You could change all the formulas in mytab to plain old text

Select all the cells
edit|replace
what: =
with: $$$$$=
replace all

Then do the move|copy.

Then do the reverse
select all the cells
edit|replace
what: $$$$$=
with: =
replace all

And remember to close the original workbook without saving or fix it there, too.

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?

 

[Guest]

meatshield and Dave Peterson: thanks much! Both solutions work just as I
wanted. I knew this had to be easy to do!

You could change all the formulas in mytab to plain old text

Select all the cells
edit|replace
what: =
with: $$$$$=
replace all

Then do the move|copy.

Then do the reverse
select all the cells
edit|replace
what: $$$$$=
with: =
replace all

And remember to close the original workbook without saving or fix it there, too.

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?

 

[Guest]

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?

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.

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?

 

[meatshield]

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

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?

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.

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?