Excel Worksheets.Add

[rnordquest]

These used to work just fine but now I get an error:

Set Summary = Worksheets.Add
Summary.Name = "Summary"


Set NewSheet = Worksheets.Add
NewSheet.Name = "Spec_Summary"

I get:

Run-time error'1004'
Method 'Add' of object 'Sheets" failed

Any ideas on what caused this or what I can do to fix it?

Thanks,

Roger

 

[jghrist]

Have you upgraded to a different version of Excel? I often find errors like this after upgrading because Microsoft changes little things.

 

[rnordquest]

Not that I know of. I'll have to check. We're supposed to move to Office 2003 but that hasn't happened yet.

 

[InspEngr]

Has Excel possibly been recently re-installed? Each time a machine is "re-imaged" (or a new machine is installed) at work, we have to install a couple of the add-ins to get some of our regularly-used functions to work (such as "EDATE" in the Analysis ToolPak).

Looking at the ToolPaks available here, a couple have appear to be VBA related, i.e. Analysis ToolPak - VBA.

The above probably doesn't apply, but might be worth pursuing.

-InspEngr

 

[joerd]

Have you tried manually adding a worksheet (Insert/Worksheet in Excel)? Maybe the workbook is protected, or something similar.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[rnordquest]

joerd,

You are right as always. I can't insert a worksheet manually. However, neither the workbook or worksheet is protected as far as I can tell. I can open, edit, and save without a password and it's not shared; but I can't insert a worksheet. What am I missing?

 

[rnordquest]

joerd,

I figured it out. I had locked a few cells and password protected as a test. I placed this out on the network so others would have access and we were playing around with security ideas weeks ago. I forgot to remove the protection when we were finished. I took a stab at passwords for awhile until I got it right. It was something I've never used before or since so blind luck and stupidity has saved my butt again. Now where did I put those lottery numbers.