I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is does
not give the unprotect option.

THanks

Please also note that I can Insert Worksheets manually. I
don't get it???


-----Original Message-----
I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is does
not give the unprotect option.

THanks
.

"ExcelMonkey" wrote in message
...
I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is does
not give the unprotect option.

THanks

Maybe the code tries to add a worksheet with a name that already exists.

/Fredrik

"ExcelMonkey" wrote in message
...
I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is does
not give the unprotect option.

THanks

Maybe the code tries to add a worksheet with a name that already exists.

/Fredrik

What is in AuditShtName? Sure it is not empty or a duplicate name?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote in message
...
Please also note that I can Insert Worksheets manually. I
don't get it???


-----Original Message-----
I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is does
not give the unprotect option.

THanks
.

No Bob it has a name - "Audit Results". This is odd as
this work on all other files. And there is not a sheet in
the file called "Audit Result".

If this workbook were protected or Read only - how would I
know?


-----Original Message-----
What is in AuditShtName? Sure it is not empty or a
duplicate name?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote
in message
...
Please also note that I can Insert Worksheets
manually. I
don't get it???


-----Original Message-----
I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I
just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is
does
not give the unprotect option.

THanks
.



.

Activeworkbook.Readonly will tell you the first, but that doesn't stop you
adding sheets, just saving it.

You get the goodies don't you :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote in message
...
No Bob it has a name - "Audit Results". This is odd as
this work on all other files. And there is not a sheet in
the file called "Audit Result".

If this workbook were protected or Read only - how would I
know?


-----Original Message-----
What is in AuditShtName? Sure it is not empty or a
duplicate name?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote
in message
...
Please also note that I can Insert Worksheets
manually. I
don't get it???


-----Original Message-----
I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I
just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is
does
not give the unprotect option.

THanks
.



.

Yes I do get the goodies. But its making me a better
person :-)


-----Original Message-----
Activeworkbook.Readonly will tell you the first, but that
doesn't stop you
adding sheets, just saving it.

You get the goodies don't you :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote
in message
...
No Bob it has a name - "Audit Results". This is odd as
this work on all other files. And there is not a sheet
in
the file called "Audit Result".

If this workbook were protected or Read only - how
would I
know?


-----Original Message-----
What is in AuditShtName? Sure it is not empty or a
duplicate name?

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"ExcelMonkey"
wrote
in message
...
Please also note that I can Insert Worksheets
manually. I
don't get it???


-----Original Message-----
I have a an macro that always works. It an addin
which
creates an summary sheet in the active workbook. I
just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is
does
not give the unprotect option.

THanks
.



.



.

Any hidden sheets in the workbook?

If so, try unhiding them and then see if the code works.

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
No Bob it has a name - "Audit Results". This is odd as
this work on all other files. And there is not a sheet in
the file called "Audit Result".

If this workbook were protected or Read only - how would I
know?


-----Original Message-----
What is in AuditShtName? Sure it is not empty or a
duplicate name?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote
in message
...
Please also note that I can Insert Worksheets
manually. I
don't get it???


-----Original Message-----
I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I
just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is
does
not give the unprotect option.

THanks
.



.

Workbook level protection (structure option) is what would prevent adding
sheets, but it would prevent you from doing it manually. If you did
Tools=Protection and it offered Unprotect Workbook, then you would know it
is protected.

If you are in Excel 97 or this file was used extensively in Excel 97, then
go into the project explorer in the VBE and see if you have code names for
your sheets like

Sheet11111111111111111111111111111

These type of names can be problematic if they are too long - around 31
characters as I recall.

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
...
No Bob it has a name - "Audit Results". This is odd as
this work on all other files. And there is not a sheet in
the file called "Audit Result".

If this workbook were protected or Read only - how would I
know?


-----Original Message-----
What is in AuditShtName? Sure it is not empty or a
duplicate name?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote
in message
...
Please also note that I can Insert Worksheets
manually. I
don't get it???


-----Original Message-----
I have a an macro that always works. It an addin which
creates an summary sheet in the active workbook. I
just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is
does
not give the unprotect option.

THanks
.



.

Next time you're in the VBE, you can select the worksheet (from the project
explorer) and hit F4 to see its properties.

Change the (Name) (with the parentheses) to something smaller in length.

sheet71 is not the problem.

Sheet11111111111111111111111111 is the problem. (31 characters is the maximum)

I'd fix any (Name) property that's over 7 characters long (sheet##).



ExcelMonkey wrote:

So Tom there are code names for the sheets in the Project
Window. These are the only ones that seem funny:
Sheet16111
Sheet1611111
Sheet16111111
Sheet3111

There are also sheets that go as high as:
Sheet71. Not sure if this matters.

Regardless, what can I do abou this to fix it?

Thanks for you help.

-----Original Message-----
Workbook level protection (structure option) is what
would prevent adding
sheets, but it would prevent you from doing it
manually. If you did
Tools=Protection and it offered Unprotect Workbook, then
you would know it
is protected.

If you are in Excel 97 or this file was used extensively
in Excel 97, then
go into the project explorer in the VBE and see if you
have code names for
your sheets like

Sheet11111111111111111111111111111

These type of names can be problematic if they are too
long - around 31
characters as I recall.

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote
in message
...
No Bob it has a name - "Audit Results". This is odd as
this work on all other files. And there is not a sheet
in
the file called "Audit Result".

If this workbook were protected or Read only - how
would I
know?


-----Original Message-----
What is in AuditShtName? Sure it is not empty or a
duplicate name?

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"ExcelMonkey"
wrote
in message
...
Please also note that I can Insert Worksheets
manually. I
don't get it???


-----Original Message-----
I have a an macro that always works. It an addin
which
creates an summary sheet in the active workbook. I
just
ran it on a file and recieved this message:

Error 1004 Method 'Add'of Object Sheets failed

the line of code it is failing on is this:

.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = AuditShtName

Why would this be failing? Could this be a workbook
protection issue? When I go to Tools\Protection\ is
does
not give the unprotect option.

THanks
.



.



.


--

Dave Peterson