Changing Sheet Cell Formula Reference

[David]

I have two issues both similar:

1) I have a number of columns, one of which has a formula with a Sheet!Cell
reference in the formula. I want to change the Sheet reference to another
sheet and previous the rest of the formula in that column. For example:

Old Sheet Name: DoThis
New Sheet Name: GoHere

---> Can some loop be constructed using For Each in maybe a VB module which
would do this? If so what is the object name and how would I do it?

----> Is there an easier way?

================

2) Basically the same issue, except the link is in another workbook.
I want to remove the linked workbook and move the single sheet on the
Workbook to another Workbook with multiple sheets?

 

[JBeaucaire]

I just use the CTRL-H search/replace function for this kind of thing.

 

[OssieMac]

Hi David,

Ensure that you back up your workbooks before you do the following in case
it does not work as expected.

In your question 1 does your other sheet already exist? If so, then
Find/Replace should work.

Question 2. If you open all 3 workbooks concerned then you should be able to
right click the worksheet name tab of the worksheet to be moved and select
Move or Copy and simply move it to the other workbook and Excel should look
after the links for you.

 

[David]

OssieMac:

Thanks got it done. Copy worked great.
Only issue was it keep the links to the other workbook on the copied sheet.

ALso found a MS "Q" with an XLA which will find links.

Appreciate the Assist.
David