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
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
Does this help?
Sub judeprem() With ActiveSheet .UsedRange.Value = .UsedRange.Value End With End Sub
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
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
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.
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
This worked for me, when I tested it?
Sub judeprem() Dim x As Range For Each x In ActiveSheet.UsedRange If x.Formula Like "*Master*" Then x.Value = x.Value Next x End Sub
Hi John,
I tried and it didn't work, am i doing something wrong.
I have attached the sample file.
Many thanks
Jude
No you weren't doing anything wrong. In order to use UsedRange there needs to be a value in Range("A1").
Try this:
Also added Option Compare Text above the code so the sub would recognize "MASTER", "Master" or "master".Option Compare Text Sub judeprem() Dim x As Range Range("A1") = " " For Each x In ActiveSheet.UsedRange If x.Formula Like "*Master*" Then x.Value = x.Value Next x End Sub
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
Try:
Option Compare Text Sub judeprem() Dim x As Range, ws As Worksheet For Each ws In ActiveWorkbook.Worksheets If ws.Range("A1") = "" Then ws.Range("A1") = " " For Each x In ws.UsedRange If x.Formula Like "*Master*" Then x.Value = x.Value Next x Next ws End Sub
Hi John
Great, it worked, Many thanks once again.
Regards
Jude
You're welcome. Glad to help out and thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks