link to other worksheet doesn't show

[anaksyap]

Hello!

Hopefully someone can help me with this one:

I created a worksheet including formulas that look up into other two worksheets based on the tab name:

=VLOOKUP($B8,INDIRECT("'M:\Accounting\Controlling\Monthly Reports\FY20\March\P&Ls\[2019 P&Ls.xlsx]"&INDEX(tab,MATCH(1,--(COUNTIF(INDIRECT("'M:\Accounting\Controlling\Monthly Reports\FY20\March\P&Ls\[2019 P&Ls.xlsx]"&tab&"'!$A:$n"),$B8)>0),0))&"'!$A:$n"),2,FALSE)

Now I need to break the links so I can send out the worksheet but the edit links function is deactivated :S. Any ideas of how I can transform all formulas into values? (its a huge worksheet so copy/paste special wouldn't do)

Thank you!

[#2]

@anaksyap I'm afraid links inside INDIRECT formulas are not picked up by the Edit Links dialog. Copy/paste special values is all you have I'm afraid.

Perhaps this little macro will do?

Sub ConvertToValues()
    Dim Sh As Worksheet
    If MsgBox("Are you SURE you want to convert everything to values? (No UNDO!!)", vbYesNo) = vbNo Then Exit Sub
    For Each Sh In Worksheets
        Sh.UsedRange.Value = Sh.UsedRange.Value
    Next
End Sub