Can't insert a new worksheet

[Jack Deuce]

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's 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
password).

Thanks in advance.

 

[Dave Peterson]

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 doesn't 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've
deleted a few.

 

[Jack Deuce]

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]

If you start excel in safe mode:

Close excel
Windows start button|Run
type:
excel /safe

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

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

 

[Gord Dibben]

What did you mean by

"None of the workbooks have passwords but all are protected (without a
password)."?

If a workbook is protected, you cannot insert sheets.


Gord Dibben MS Excel MVP

 

[Jack Deuce]

The Excel File itself it NOT password protected. You don't need a
password to open it. The workbook IS protected from
TOOLS,PROTECTION,PROTECT SHEET. The workbook has been UNPROTECTED in
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]

Tried what you suggested, took PROTECTION OFF, can't 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
failing.

Thanks for the suggestions...

 

[Dave Peterson]

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's an option to protect the workbook's structure in that last type.
You may want to look for this type once more.

 

[Jack Deuce]

Thanks Dave,

That was the problem. The entire workbook was protected.

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

Thanks for all your trouble solving this problem.