Macro to remove cell reference to another worksheet and external links

[lalaarif1]

Dear Friends,

I have 2 worksheets (sheet A and sheet B)

Sheet B contains formula linking to sheet A.
(e.g In sheet B
B1 = 'Sheet A'!C1 (10)
B2 = 'Sheet A'!C2 (20)
B3 = B1 + B2

Is there a macro which will remove reference of another sheet ?
e.g In sheet B
B1 = 10
B2 = 20
B3 = B1 + B2

Also, Is there a macro which will remove reference of another workbook. ?

I have many worksheets with numerous links.
I would like to remove the links of particular sheet only. Its very time consuming to find the reference and then do "copy paste special"

I want a macro to remove all the references and links in the selected sheet.

Please assist.

[oeldere]

How about copy paste special for the whole worksheet?

[jason.b75]

How about copy paste special for the whole worksheet?

That would replace the formula in B3 as well!

Also, Is there a macro which will remove reference of another workbook. ?

Do you want to break the external link, but keep the formula (referring to the same sheet and range in the existing workbook), or convert the cell to value?

[lalaarif1]

I want to break the external references/links only.
Formula referring to the same sheet should not be touched. It will remain as formula only.

[jason.b75]

Save a backup copy of your file, then try this.

To break links to other workbooks

https://support.office.com/en-gb/art...5-f4fdb1442748

To break links to other sheets in the same workbook

Please Login or Register  to view this content.

The code will ask you to 'enter criteria' which should be the name of the sheet to search for in the formulas.

Does that do what you need?

[lalaarif1]

Jason,

Thanks for reply.
But sorry. Macro is not serving my purpose.

I do not want any pop massage asking for the criteria.
I just want the macro to break all the links in the sheet. (be it links from another sheet or links from another workbook)
Formula referring within the sheet should remain as formula.

Please assist

[jason.b75]

I would like to remove the links of particular sheet only.

I based the code on what you asked for, "particular sheet" implies that there could be multiple sheets but you only want to remove links to one.

This code will not be selective, any formula containing the ! character will be converted to value.

Please Login or Register  to view this content.

[lalaarif1]

Wow ....

Thanks a lot. You made my day. Macro is working fine.

God bless you.

[lalaarif1]

Jason,

Can I also have macro which will break all the links in the workbook (be it links from another sheet or links from another workbook)?
Formula referring within the sheets should remain as formula.

In my file, there are 40 sheet.
Sometime I need to break links of only few sheets and sometimes all the sheets.

Please assist.

[lalaarif1]

Jason,

I want following option also.
Is there a possibility in macro which displays names of all the sheets as pop up.
Then I can select the sheets where links need to be removed.

Please assist.

[jason.b75]

Try this one

Please Login or Register  to view this content.

If you have a lot of formulas then it could take a while.

The link that I provided in post #5 shows you how to break the links to other workbooks with a couple of mouse clicks, so that might be quicker than running the macro.

[lalaarif1]

Macro is showing debug error.

Also let me know what criteria i need to give in pop up (refer post 5) and how the macro will work.

[jason.b75]

If that is happening then I would guess that some sheets don't have any formulas at all.

This should fix that, also added in a bit of a speed boost.

Please Login or Register  to view this content.

[jason.b75]

Also let me know what criteria i need to give in pop up (refer post 5) and how the macro will work.

Forget the macro there, follow the link and then the instructions under the heading 'Break a link'.

You only need to do this if you have a lot of links to other workbooks and the macro is too slow.

[lalaarif1]

Thanks a lot.

Macro is working fine.

Sorry to bother u again.

Is there a possibility in macro which displays names of all the sheets as pop up.
Then I can select the sheets where links need to be removed.

I tried using macro of post 5. I could not understand it.

Please assist.

[jason.b75]

That could start getting a bit messy, as an alternative, this variation of the code should only run on the selected sheets. (select multiple sheets by holding Ctrl and clicking the tabs).

Keep the previous version so you can run it on all sheets without selecting them first if you need to.

Please Login or Register  to view this content.

Is that adequate?