Deleted a bunch of tabs and now my main sheet doesn't work.

[de8212]

I'll try to explain this the best I can.
I have on workbook with a main sheet called Report and a bunch of other tabs 1, 2, 3, 4, and so on for the days of the month. The Report sheet pulls alot of data from 1, 2, 3,4, etc.

So, I noticed an error on the day of the month tabs. To save time, I just made the adjustment on tab 1 and deleted the 2-31 tabs. Then went back and made a copy of 1 to a new tab and renamed it to 2. Did that same for the rest of the days.
But now, none of my formulas on the Report tab (which cell reference the others) work correctly. They all say #REF! and then the column/row. UGH.

How screwed am I?

I do have a copy and can resort back to the original with the error. Not really a huge deal but I would like to salvage this if possible.

 

[Joe4]

If you deleted tabs that formulas on other tabs referenced, those "lost" references will all display with the #REF error.
Short of going in and manually updating each #REF error to what it should be now, I don't think that there is any way to fix it after they have been deleted.

Moral of the story: Do not delete any tabs that formulas on other tabs reference. You can rename them, but don't delete them.

 

[de8212]

Thanks Joe.
So, since I still have the original, untouched file, is there another way to do what I am trying to do without breaking the cell referencing?

The only way I know of is to go into each tab (1-31) and unprotect it, delete or hide the column (which is all I really need to do) and then password protect again.
Not terrible but I was trying to find a quicker way.

 

[Joe4]

If you delete the tabs or the ranges the formulas are referring to, you are going to get those #REF errors, and there is really no good way to fix them once they occur because they have no "memory" of what they were before.

But why not just clear the values in the ranges on the existing tabs? You can clear the value. As long as you do not delete the tabs or the ranges, your formulas will stay intact.
You can even create some VBA code to automatically clear the contents on all the tabs with a single click to make it really easy.

 

[de8212]

If I understand you, I can (and did) go into each tab and make the change individually. It wasn't terrible but it took a while.
I'm not familiar enough with VBA to come up with a way to do something like this automatically.
I do appreciate your time.

thanks

 

[Joe4]

I'm not familiar enough with VBA to come up with a way to do something like this automatically.
I do appreciate your time.

We can probably help you with that part, if you let us know the rules (i.e. which ranges need to be cleared on each sheet).
You don't even necessarily need to know exactly which row to end on, as long as you can tell us how to identify the last row with data, i.e. every row with data has a an entry in column A, so we can use column A to dynamically identify the last row of data.