I have several worksheets with cells that are linked to other worksheets and
to other workbooks. Those other sheets and workbooks are now obsolete and I
need to paste the current values of those linked cells with their current
values....i.e., search the worksheet line by line, find a linked cell, copy
and paste its current LINKED value into that cell (thus overwriting the
link...), then continue searching for the next linked cell and do the same
until the linked values of the cells in entire worksheet have been replaced
with their current linked values.

Is there a function to do this? Or, is a macro the only way? If macro,
then how do I write it?

Thanks..........TomCat

Hi TomCat:

See:

http://support.microsoft.com/default...;en-us;q188449


--
Gary's Student


"TomCat" wrote:

I have several worksheets with cells that are linked to other worksheets and
to other workbooks. Those other sheets and workbooks are now obsolete and I
need to paste the current values of those linked cells with their current
values....i.e., search the worksheet line by line, find a linked cell, copy
and paste its current LINKED value into that cell (thus overwriting the
link...), then continue searching for the next linked cell and do the same
until the linked values of the cells in entire worksheet have been replaced
with their current linked values.

Is there a function to do this? Or, is a macro the only way? If macro,
then how do I write it?

Thanks..........TomCat

Didn't work. It just stalled.

Are there any other ways of doing this?

"Gary''s Student" wrote:

Hi TomCat:

See:

http://support.microsoft.com/default...;en-us;q188449


--
Gary's Student


"TomCat" wrote:

I have several worksheets with cells that are linked to other worksheets and
to other workbooks. Those other sheets and workbooks are now obsolete and I
need to paste the current values of those linked cells with their current
values....i.e., search the worksheet line by line, find a linked cell, copy
and paste its current LINKED value into that cell (thus overwriting the
link...), then continue searching for the next linked cell and do the same
until the linked values of the cells in entire worksheet have been replaced
with their current linked values.

Is there a function to do this? Or, is a macro the only way? If macro,
then how do I write it?

Thanks..........TomCat

Yes:


Look for FindLink in:

http://www.bmsltd.ie/MVP/Default.htm

--
Gary's Student


"TomCat" wrote:

Didn't work. It just stalled.

Are there any other ways of doing this?

"Gary''s Student" wrote:

Hi TomCat:

See:

http://support.microsoft.com/default...;en-us;q188449


--
Gary's Student


"TomCat" wrote:

I have several worksheets with cells that are linked to other worksheets and
to other workbooks. Those other sheets and workbooks are now obsolete and I
need to paste the current values of those linked cells with their current
values....i.e., search the worksheet line by line, find a linked cell, copy
and paste its current LINKED value into that cell (thus overwriting the
link...), then continue searching for the next linked cell and do the same
until the linked values of the cells in entire worksheet have been replaced
with their current linked values.

Is there a function to do this? Or, is a macro the only way? If macro,
then how do I write it?

Thanks..........TomCat

Nope. Struck out again. Didn't do what I wanted it to do. I need it to
search a line for a cell that has a link, copy that value, delete the link,
then paste that value back in the cell.

Isn't there a quick macro that can do that? I've tried Hlookups, Indexes,
everything and can't quite get there.

"Gary''s Student" wrote:

Yes:


Look for FindLink in:

http://www.bmsltd.ie/MVP/Default.htm

--
Gary's Student


"TomCat" wrote:

Didn't work. It just stalled.

Are there any other ways of doing this?

"Gary''s Student" wrote:

Hi TomCat:

See:

http://support.microsoft.com/default...;en-us;q188449


--
Gary's Student


"TomCat" wrote:

I have several worksheets with cells that are linked to other worksheets and
to other workbooks. Those other sheets and workbooks are now obsolete and I
need to paste the current values of those linked cells with their current
values....i.e., search the worksheet line by line, find a linked cell, copy
and paste its current LINKED value into that cell (thus overwriting the
link...), then continue searching for the next linked cell and do the same
until the linked values of the cells in entire worksheet have been replaced
with their current linked values.

Is there a function to do this? Or, is a macro the only way? If macro,
then how do I write it?

Thanks..........TomCat

edit/links/break links,leaves the value thats there if when you first open
the sheet and it asks you to update say no,then go to edit
menu/links/breaklinks.......
you could use find using a phrase or prt phrase from your old links to find
them
--
paul

remove nospam for email addy!



"TomCat" wrote:

Nope. Struck out again. Didn't do what I wanted it to do. I need it to
search a line for a cell that has a link, copy that value, delete the link,
then paste that value back in the cell.

Isn't there a quick macro that can do that? I've tried Hlookups, Indexes,
everything and can't quite get there.

"Gary''s Student" wrote:

Yes:


Look for FindLink in:

http://www.bmsltd.ie/MVP/Default.htm

--
Gary's Student


"TomCat" wrote:

Didn't work. It just stalled.

Are there any other ways of doing this?

"Gary''s Student" wrote:

Hi TomCat:

See:

http://support.microsoft.com/default...;en-us;q188449


--
Gary's Student


"TomCat" wrote:

I have several worksheets with cells that are linked to other worksheets and
to other workbooks. Those other sheets and workbooks are now obsolete and I
need to paste the current values of those linked cells with their current
values....i.e., search the worksheet line by line, find a linked cell, copy
and paste its current LINKED value into that cell (thus overwriting the
link...), then continue searching for the next linked cell and do the same
until the linked values of the cells in entire worksheet have been replaced
with their current linked values.

Is there a function to do this? Or, is a macro the only way? If macro,
then how do I write it?

Thanks..........TomCat

Got it.......THANK YOU!!!!!

TomCat

"paul" wrote:

edit/links/break links,leaves the value thats there if when you first open
the sheet and it asks you to update say no,then go to edit
menu/links/breaklinks.......
you could use find using a phrase or prt phrase from your old links to find
them
--
paul

remove nospam for email addy!



"TomCat" wrote:

Nope. Struck out again. Didn't do what I wanted it to do. I need it to
search a line for a cell that has a link, copy that value, delete the link,
then paste that value back in the cell.

Isn't there a quick macro that can do that? I've tried Hlookups, Indexes,
everything and can't quite get there.

"Gary''s Student" wrote:

Yes:


Look for FindLink in:

http://www.bmsltd.ie/MVP/Default.htm

--
Gary's Student


"TomCat" wrote:

Didn't work. It just stalled.

Are there any other ways of doing this?

"Gary''s Student" wrote:

Hi TomCat:

See:

http://support.microsoft.com/default...;en-us;q188449


--
Gary's Student


"TomCat" wrote:

I have several worksheets with cells that are linked to other worksheets and
to other workbooks. Those other sheets and workbooks are now obsolete and I
need to paste the current values of those linked cells with their current
values....i.e., search the worksheet line by line, find a linked cell, copy
and paste its current LINKED value into that cell (thus overwriting the
link...), then continue searching for the next linked cell and do the same
until the linked values of the cells in entire worksheet have been replaced
with their current linked values.

Is there a function to do this? Or, is a macro the only way? If macro,
then how do I write it?

Thanks..........TomCat