Excel - Can't insert a new worksheet

Asked By Jack Deuce on 31-Jul-10 04:49 PM
I have 4 spreadsheets, one for each school, each containing 100+
worksheets with the same worksheet names.  The only difference between
them is the data (inventory) that is recorded in each.  it is time to
roll the totals for next year and add new products.  All 4 have been
unprotected and 3 of them will allow the inserting of new worksheets,
but the 4th when I right-click on an adjoining tab is displaying
grayed out 'INSERT, DELETE, RENAME, MOVE or COPY'.  I need to insert
new worksheets for new products.  Does anyone have any ideas as to why
this one workbook would disallow the insertion of a new worksheet?

None of the workbooks have passwords but all are protected (without a

Thanks in advance.

Dave Peterson replied to Jack Deuce on 31-Jul-10 06:27 PM
I would double check to make sure that the workbook protection has been removed.

If it has, try closing other applications/workbooks.  The maximum number of
sheets in any workbook is limited by available memory.

If that does not help, I'd try this against a copy of the workbook.  See if you
can remove worksheets.  If you can, then see if you can add more after you have
deleted a few.

Dave Peterson
Jack Deuce replied to Dave Peterson on 31-Jul-10 07:26 PM
From a copy of the workbook I cannot delete any worksheets because the
delete function is not available (greyed-out) when I select a
worksheet to disable.  No other apps running either. Protection is
OFF. ????
Dave Peterson replied to Jack Deuce on 01-Aug-10 07:08 AM
If you start excel in safe mode:

Close excel
Windows start button|Run
excel /safe

Then use file|open to open your file, can you delete/insert sheets?

If yes, then maybe you have a macro that is disabling those options.

Dave Peterson
Gord Dibben replied to Jack Deuce on 01-Aug-10 11:19 AM
What did you mean by


If a workbook is protected, you cannot insert sheets.

Gord Dibben     MS Excel MVP
Jack Deuce replied to Gord Dibben on 02-Aug-10 02:06 PM
The Excel File itself it NOT password protected.  You do not need a
password to open it.  The workbook IS protected from
that sense.  The other 3 workbooks are basically clones of each other.
the only difference between them is the data that is stored in them.
IOW, I create one workbook and save it 4 times to get each school
their own file to track inventory (food items).

Hope that helps.
Jack Deuce replied to Dave Peterson on 02-Aug-10 02:11 PM
Tried what you suggested, took PROTECTION OFF, cannot insert or delete.
Also checked to see there are no macros referenced in this workbook.

thanks for the suggestion.  I went back to last year version and
modified it for this year to get around the problem.  Everything is
fine now though am still puzzled as to why just that one workbook is

Thanks for the suggestions...
Dave Peterson replied to Jack Deuce on 02-Aug-10 02:39 PM
Excel offers different levels of protection.

You've seen the file level password to open (or password to modify).

You've see worksheet level (Tools|protection|protect sheet)

But it also offers a workbook level protection:
Tools|Protection|protect workbook

And there is an option to protect the workbook's structure in that last type.
You may want to look for this type once more.

Dave Peterson
Jack Deuce replied to Dave Peterson on 04-Aug-10 07:50 AM
Thanks Dave,

That was the problem.  The entire workbook was protected.

I generally use ASAP Utilities function to PROTECT and UNPROTECT the
sheets. I am assuming this one workbook was somehow PROTECTED using
TOOLS instead.

Thanks for all your trouble solving this problem.