Copying sheets with names, macros and functions

[Guest]

I have a worksheet I have developed over several years. I have names that are used with other sheets within the workbook and macro's and user defined functions. The workbook works fine until I copy a sheet. It will let me copy the sheet and make changes and save. However, when I go to close the worksheet, by clicking on the X, it creates an error and shuts down. When I go to open it, it shuts down excel and I can't open it again. However, if I go to the File menu and choose exit, to exit excel, it will let me back in. Also the other day when I did this, I noticed when I went back in that Excel had changed the calculations on the Tools menu under Options to manual calculations
Why is Excel doing this
What can I do to correct this problem

 

[Dave Peterson]

Don't have a guess about the calculation mode stuff.

But you may want to try rebuilding that problem worksheet. (or at least a
little portion of it.)

If that works ok, I'd rebuild the whole thing and dump the original sheet. (I
had a similar problem and recreating the worksheet fixed the problem--the
worksheet was corrupt.)

But I've only ever had a couple of those corrupt worksheets--so in my
experience, they don't happen often.

But if you only rebuild a little portion, you won't lose too much time if turns
out to be a waste.

 

[Guest]

Dave, When you say rebuild, do you mean rebuilding the
names, macros and functions or everything. I have a lot
of data I would like to copy and paste or will that mess
it up. Thanks for your response. Phil

-----Original Message-----
Don't have a guess about the calculation mode stuff.

But you may want to try rebuilding that problem worksheet. (or at least a
little portion of it.)

If that works ok, I'd rebuild the whole thing and dump the original sheet. (I
had a similar problem and recreating the worksheet fixed the problem--the
worksheet was corrupt.)

But I've only ever had a couple of those corrupt worksheets--so in my
experience, they don't happen often.

But if you only rebuild a little portion, you won't lose too much time if turns
out to be a waste.

years. I have names that are used with other sheets
within the workbook and macro's and user defined
functions. The workbook works fine until I copy a sheet.
It will let me copy the sheet and make changes and save.
However, when I go to close the worksheet, by clicking on
the X, it creates an error and shuts down. When I go to
open it, it shuts down excel and I can't open it again.
However, if I go to the File menu and choose exit, to exit
excel, it will let me back in. Also the other day when I
did this, I noticed when I went back in that Excel had
changed the calculations on the Tools menu under Options
to manual calculations.

 

[Dave Peterson]

When I rebuilt one worksheet, I copied|paste special formulas. I redid the
formatting by hand (and dropped tons of "ransom note" formatting--each cell a
different font/color, etc).

I had to rebuild the range names manually. But I didn't touch any of the VBA
code. The corruption was at the worksheet level--not workbook (in my case).

I'm not sure if this is the easiest way, but here's what I did.

I copied my workbook to a new folder (using windows explorer).

I opened that workbook and moved the problem worksheet to a brand new workbook.

Then I saved the new workbook (in that same folder as Problem.xls) and the
original workbook (without the problem worksheet).

Any formulas that referred to this sheet now had external references to a new
workbook.

Then I closed that original workbook.

And I started a new workbook and did my best to copy (no formatting & comments)
to a new worksheet in that new workbook.

I named the new worksheet in the new workbook the same name as the problem
sheet.

And I saved the new workbook (as NotAProblem.xls) in that same folder. And
closed excel.

So I had 3 workbooks in that folder:

The Original (with links to the problem worksheet)
the problem.xls
the brand new workbook (with all the formulas and values)

Using windows explorer,
I renamed problem.xls to badproblem.xls.
I renamed notaproblem.xls to problem.xls

But the links in the original.xls still pointed at problem.xls (it had no idea
what I was doing in windows explorer with excel closed!)

Then I opened original.xls and problem.xls (which is really the "good" one.)

I moved that worksheet back into original.xls and the links adjust to just point
at the worksheet within that workbook.

I may have gotten lucky, but it worked ok for me.

It was quite a while ago and I don't recall having problems with workbook names,
but I may have had to clean some of those up.

A very nice utility that you may want to use before you start (just to see
what's up):

Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager.
http://www.jkp-ads.com/Download.htm

It'll make the review easier and maybe any cleanup.

And if you want to make sure you don't have any "extra" links to workbooks after
you're done:

Bill Manville's FindLink program:
http://www.bmsltd.ie/MVP/Default.htm

======
But the worst that'll happen is it might not work exactly correct. But since
you're working on copies, you can learn from your mistakes (I did) and just
start over.

======

And one more thing. I have no idea if this is true, but the sheet that got
corrupted had a bunch of different type formatting on it. And it also had a ton
of comments.

I blame the comments (and the formatting). But it's more of a gut feeling. I
don't have any real proof. But it has a lot less of both now (and still
running w/o a problem--knock wood!). (I think I used xl97 to do all this stuff
and we use xl2k--so it has been a while.)