Change Multiple "Refers To" in Name Manager at once

Binning · Dec 3rd 2014

Hi there,

I'm currently facing an issue whereby a raw data worksheet that a lot of other sheets make reference to has become corrupt (formatting on millions of cells and unable to delete the rows / columns without excel showing a message that there are unavailable resources). I'm doing this on a company computer so there is no opportunity for me to download any sort of add-in for to make the adjustments.

Currently, the "Refers To" in the Name Manager is listed as 'Raw Data Balances'. Since I have made a copy of the sheet to try and salvage the data, I would like to change it to 'Raw Data Balances(2)'.

This is the only way I imagine it can be fixed, as deleting the sheet entirely will mess up the formulas everywhere else. If you have any other suggestions please let me know.

Thanks,
Binning

**royUK** · Dec 3rd 2014

Re: Change Multiple "Refers To" in Name Manager at once

If you really have millions of cells with formatting that is probably the cause of your problem.

Do all names refer to the same 'Raw Data Balances'?

Haven't you got a back up copy from before the error occurred?

Binning · Dec 4th 2014

Re: Change Multiple "Refers To" in Name Manager at once

Quote from royUK;732962

If you really have millions of cells with formatting that is probably the cause of your problem.

Do all names refer to the same 'Raw Data Balances'?

Haven't you got a back up copy from before the error occurred?

Hi Roy,

The majority of the names refer to 'Raw Data Balances'. It's a pretty poorly designed spreadsheet and this problem is encountered a lot. I've tried almost all avenues for removing the formatted cells causing the issue but with little success. For the mean time we're using back up and inputting the data manually, I think this is really our only option at this point.

Thanks,
Binning

JohnCleary · Dec 4th 2014

Re: Change Multiple "Refers To" in Name Manager at once

Notwithstanding Roys' comment about the design, you could try

Code

Sub x()
   Dim n As Name
   
   For Each n In ThisWorkbook.Names


      
      Debug.Print n.RefersTo


      Debug.Assert False


      n.RefersTo = Replace(n.RefersTo, "Raw Data Balances", "Raw Data Balances(2)")
      Debug.Print n.RefersTo
      
   Next
   
End Sub

Display More

That simply replaces all 'Raw Data Balances' in all defined names in the workbook you run it in...

The 'Debug.Assert' is simply to stop the code so you can single step using F8 to test. It should be removed, along with the 'Debug.Print' statements later.

If you decide to use this - TEST ON A COPY OF YOUR DATA!

It's also a bit of a sledgehammer approach - as written, you can only run it once for a workbook. Re-running it will result in 'Raw Data Balances(2)(2)' and Excel assuming this is an external reference, so shows the File Open dialog.

Binning · Dec 4th 2014

Re: Change Multiple "Refers To" in Name Manager at once

Thanks John,

This seems to have done the trick.

Change Multiple "Refers To" in Name Manager at once

Participate now!

Share