Keeping correct references when replacing a corrupted sheet.

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I have a workbook with a corrupted sheet. When I copy the sheet to a new
workbook and then back again, (OR make a copy in the same workbook) and then
delete the original corrupted sheet it solves the problem but, I get a whole
bunch of #REF on another worksheet . All the formulas have a Named range
that were in the corrupted sheet. Although the copy is exactly the same and,
the named ranges still exist in the Name dialog box, they now also show the
dreaded #REF.
I expect that one way to correct this problem is to paste the name of the
sheet over the #REF in all those named ranges but, as there are quite a few,
I wonder if there is a more satisfactory way whereby I can replace a
corrupted sheet without these errors ocurring.
Could someone please advise what the best method might be?
Furthermore, it seems from trying to find where the corruption was, I found
that it is actually the worksheet itself that was corrupted and not the data
or formulas, etc., as I deleted EVERYTHING including formatting, vba,
etc.,etc., from that one sheet except cell "A1" and 1 command button. I
also deleted all empty cells and resaved to find the particular problem
still remained.
Rob
 
Rob,

Select all of the cells that you wish to copy, right-click on any of
those cells and select Copy from the drop down.

Insert a new worksheet in your workbook and right-click on A1. Now
using PasteSpecial, DO NOT use the All option but apply a bit of
thought to what you actually want.

I would suggest that Formulas would be the first, select either that or
the Formulas and Numbers option and then OK. You will only need the
one.

Back to A1 right-click and select Formats and OK.

Carry on with the exercise using perhaps using Comments if you have
any, the same with Validation and if you have diferent Column Widths
then that one should complete the transformation for you.

Rename your old sheet to something else and you new sheet to the
correct name and you should have a fully working clean copy with all
links and formulae correct.

You will have to change Row heights to match the old sheet but once you
are happy you can delete the old sheet OR better practice would be to
move it to another workbook until you are totally happy that you have
all of the data you require.

HTH
:)
 
Thanks for your reply, whisperer.
I suspect that that method would work in most situations but unfortunately I
have quite a few text boxes linked to macros, etc., which will not copy in
this manner. Furthermore, I have Array formulas which seem to prevent the
Paste Special/Format option from doing its thing.
Rob
 
Rob,

If you would like to send either a copy of the workbook or th
offending sheet I will see what I can do for you.

If it is large then please zip before sending

Gordo
 
Thanks for the offer, whisperer, but I have already fixed it all. My query
was more to do with the fact that I anticipate this situation will probably
occur in the future and want to be "pre-armed".
Have a safe new year!
Rob
 
Back
Top