Change the cell reference formula to cell value

[judeprem]

Hi All,

I have sheets which is linked each other, when i try to delete sheet the links are showing ref error.

I need a vba code to change the cell references to Cell Value in all the cells.

Any help appreciated.

Thanks
Jude

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[judeprem]

Hi John,

Apparently i need only the cell reference linked with the Main Sheet which is MASTER to become cell value, other formulas remain to be a formula

is there any to make it as value

Thanks
Prem

[judeprem]

Hi John,

One more thing some cells in the sheet having formula =SUM(a1:a10)/Master!B10, so when i delete the sheet Master the formula cell showing ref error, ideally this cell should become a value. So wherever the Master! link should become a value.

Many thanks for your help

Regards
Jude

[JOHN H. DAVIS]

Can you attach a sample sheet? Do you have specific ranges that link to Sheets Master? Range.value = Range.value will return the value only? Hope that helps.

[judeprem]

If its specific range yes i can use range.value, apparently its all over the sheets

I tried to use cell.formula like "*MASTER*" Then cell.Value = cell.value and it didn't work

[JOHN H. DAVIS]

This worked for me, when I tested it?

Please Login or Register  to view this content.

[judeprem]

Hi John,

I tried and it didn't work, am i doing something wrong.

I have attached the sample file.

Many thanks
Jude

[JOHN H. DAVIS]

No you weren't doing anything wrong. In order to use UsedRange there needs to be a value in Range("A1").

Try this:

Please Login or Register  to view this content.

Also added Option Compare Text above the code so the sub would recognize "MASTER", "Master" or "master".

[judeprem]

Hi John,

Thanks yes it worked, is it possible to make it work for all the sheets, currently it works on only one sheet

Thanks
Jude

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[judeprem]

Hi John
Great, it worked, Many thanks once again.

Regards
Jude

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.