Remove link to another sheet within the same workbook

[som3on3_10]

Hello, I need to find a way to remove links to another sheet within the same workbook. I have attached a simple example. On Sheet2, I want to make only the cells highlighted in yellow values, but keep the formula in cell A11 intact. Basically I want to be able to do what Edit Links/Break Links does, but to break the links to sheets within the same workbook. I currently have a file that has a template that builds a report and then copies and pastes the report to each respective sheet. Removing the internal linkage would make the file much faster and smaller. Any ideas? I was trying to throw around the idea of using the "!" symbol since all links have that symbol, but couldn't figure out a way to select all cells that have that symbol. Thanks!

[mehmetcik]

This will find all occurances of Sheet1! and will clear the cell.

Please Login or Register  to view this content.

[som3on3_10]

Thanks for the reply, but I'm not looking to clear the occurrences of Sheet1!, but instead I want to make it a hard-coded value as opposed to a formula. The same as doing copy-paste-values. I know I can write VBA code to do a copy-paste-values, but there are internal links are all over the report so selecting individual ranges and then doing a copy-paste-values wouldn't be very efficient. Also if I turn on option explicit, what variable type would your variables/objects be in the your code, i.e. rngLook, strValueToPick, rngFind, etc. Thanks!

[mehmetcik]

Try

Please Login or Register  to view this content.

[som3on3_10]

That works. Thanks and happy holidays!