Subscript out of range when adding worksheet
 

Using Excel 97 to 2003.
I have an add-in that somewhere has to add a worksheet after the last sheet.
This goes with:

ActiveWorkbook.Sheets.Add _
After:=Worksheets(ActiveWorkbook.Sheets.Count)

This goes fine with all the users of the add-in, except one person, who gets
the error:
Subscript out of range
at this line.

I have tried all kind of manipulations such as deleting, moving, renaming
and hiding of the sheets, but I just can't reproduce this error. Protecting
the workbook will cause a different error.
I am just puzzeled what could be causing this error.
Any advice greatly appreciated.


RBS

Subscript out of range when adding worksheet
 

The only thing that seems like it might be amiss is that you are
not qualifying the Worksheets collection. Depending on the
location of the code (e.g., in the ThisWorkbook module), it is
using ThisWorkbook.Worksheets instead of
ActiveWorkbook.Worksheets.

Try changing the code to

ActiveWorkbook.Sheets.Add _

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Sh eets.Count)


Beyond that, it all looks good to me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"RB Smissaert" wrote in message
...
Using Excel 97 to 2003.
I have an add-in that somewhere has to add a worksheet after
the last sheet.
This goes with:

ActiveWorkbook.Sheets.Add _
After:=Worksheets(ActiveWorkbook.Sheets.Count)

This goes fine with all the users of the add-in, except one
person, who gets
the error:
Subscript out of range
at this line.

I have tried all kind of manipulations such as deleting,
moving, renaming
and hiding of the sheets, but I just can't reproduce this
error. Protecting
the workbook will cause a different error.
I am just puzzeled what could be causing this error.
Any advice greatly appreciated.


RBS

Subscript out of range when adding worksheet
 

Chip,

Thanks.
I thought that Sheets always defaulted to the active workbook, unless
specified differently.
But I was indeed worried about this and that is why I did
ActiveWorkbook.Sheets.Count
So you are right I should be consequent and specify the collection of sheets
to add to as well.
Strange though that this error only happens with one person.
Will change it and come back to tell what happened.

RBS


"Chip Pearson" wrote in message
...
The only thing that seems like it might be amiss is that you are
not qualifying the Worksheets collection. Depending on the
location of the code (e.g., in the ThisWorkbook module), it is
using ThisWorkbook.Worksheets instead of
ActiveWorkbook.Worksheets.

Try changing the code to

ActiveWorkbook.Sheets.Add _

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Sh eets.Count)


Beyond that, it all looks good to me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"RB Smissaert" wrote in message
...
Using Excel 97 to 2003.
I have an add-in that somewhere has to add a worksheet after
the last sheet.
This goes with:

ActiveWorkbook.Sheets.Add _
After:=Worksheets(ActiveWorkbook.Sheets.Count)

This goes fine with all the users of the add-in, except one
person, who gets
the error:
Subscript out of range
at this line.

I have tried all kind of manipulations such as deleting,
moving, renaming
and hiding of the sheets, but I just can't reproduce this
error. Protecting
the workbook will cause a different error.
I am just puzzeled what could be causing this error.
Any advice greatly appreciated.


RBS

Subscript out of range when adding worksheet
 

RBS

Are there any non-worksheet sheets (charts, macros) in the workbook?

Change the after arg to

After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets .Count)

Using Sheets.Count as the Item identifier of Worksheets may be where the
problem is. Alternatively you may be able to use

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Wo rksheets.Count)

But that may not put the new worksheet at the end of *all* sheets.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"RB Smissaert" wrote in message
...
Chip,

Thanks.
I thought that Sheets always defaulted to the active workbook, unless
specified differently.
But I was indeed worried about this and that is why I did
ActiveWorkbook.Sheets.Count
So you are right I should be consequent and specify the collection of
sheets
to add to as well.
Strange though that this error only happens with one person.
Will change it and come back to tell what happened.

RBS


"Chip Pearson" wrote in message
...
The only thing that seems like it might be amiss is that you are
not qualifying the Worksheets collection. Depending on the
location of the code (e.g., in the ThisWorkbook module), it is
using ThisWorkbook.Worksheets instead of
ActiveWorkbook.Worksheets.

Try changing the code to

ActiveWorkbook.Sheets.Add _

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Sh eets.Count)


Beyond that, it all looks good to me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"RB Smissaert" wrote in message
...
Using Excel 97 to 2003.
I have an add-in that somewhere has to add a worksheet after
the last sheet.
This goes with:

ActiveWorkbook.Sheets.Add _
After:=Worksheets(ActiveWorkbook.Sheets.Count)

This goes fine with all the users of the add-in, except one
person, who gets
the error:
Subscript out of range
at this line.

I have tried all kind of manipulations such as deleting,
moving, renaming
and hiding of the sheets, but I just can't reproduce this
error. Protecting
the workbook will cause a different error.
I am just puzzeled what could be causing this error.
Any advice greatly appreciated.


RBS

Subscript out of range when adding worksheet
 

Dick,

Thanks, but there are only normal worksheets.
Have used Chip's suggestion, although I can't really see why it would solve
the
Subscript out of range error.
Will report back on this.

Bart


"Dick Kusleika" wrote in message
...
RBS

Are there any non-worksheet sheets (charts, macros) in the workbook?

Change the after arg to

After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets .Count)

Using Sheets.Count as the Item identifier of Worksheets may be where the
problem is. Alternatively you may be able to use

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Wo rksheets.Count)

But that may not put the new worksheet at the end of *all* sheets.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"RB Smissaert" wrote in message
...
Chip,

Thanks.
I thought that Sheets always defaulted to the active workbook, unless
specified differently.
But I was indeed worried about this and that is why I did
ActiveWorkbook.Sheets.Count
So you are right I should be consequent and specify the collection of
sheets
to add to as well.
Strange though that this error only happens with one person.
Will change it and come back to tell what happened.

RBS


"Chip Pearson" wrote in message
...
The only thing that seems like it might be amiss is that you are
not qualifying the Worksheets collection. Depending on the
location of the code (e.g., in the ThisWorkbook module), it is
using ThisWorkbook.Worksheets instead of
ActiveWorkbook.Worksheets.

Try changing the code to

ActiveWorkbook.Sheets.Add _

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Sh eets.Count)


Beyond that, it all looks good to me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"RB Smissaert" wrote in message
...
Using Excel 97 to 2003.
I have an add-in that somewhere has to add a worksheet after
the last sheet.
This goes with:

ActiveWorkbook.Sheets.Add _
After:=Worksheets(ActiveWorkbook.Sheets.Count)

This goes fine with all the users of the add-in, except one
person, who gets
the error:
Subscript out of range
at this line.

I have tried all kind of manipulations such as deleting,
moving, renaming
and hiding of the sheets, but I just can't reproduce this
error. Protecting
the workbook will cause a different error.
I am just puzzeled what could be causing this error.
Any advice greatly appreciated.


RBS

Subscript out of range when adding worksheet
 

I duplicated the out of range error using Dicks suggestion of having a non
worksheet sheet (could be hidden). If Chips suggestion doesn't work try:

Sub Tester3()
With ActiveWorkbook
..Sheets.Add _
After:=.Sheets(.Sheets.Count)
End With
End Sub

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Dick,

Thanks, but there are only normal worksheets.
Have used Chip's suggestion, although I can't really see why it would
solve
the
Subscript out of range error.
Will report back on this.

Bart


"Dick Kusleika" wrote in message
...
RBS

Are there any non-worksheet sheets (charts, macros) in the workbook?

Change the after arg to

After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets .Count)

Using Sheets.Count as the Item identifier of Worksheets may be where the
problem is. Alternatively you may be able to use

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Wo rksheets.Count)

But that may not put the new worksheet at the end of *all* sheets.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"RB Smissaert" wrote in message
...
Chip,

Thanks.
I thought that Sheets always defaulted to the active workbook, unless
specified differently.
But I was indeed worried about this and that is why I did
ActiveWorkbook.Sheets.Count
So you are right I should be consequent and specify the collection of
sheets
to add to as well.
Strange though that this error only happens with one person.
Will change it and come back to tell what happened.

RBS


"Chip Pearson" wrote in message
...
The only thing that seems like it might be amiss is that you are
not qualifying the Worksheets collection. Depending on the
location of the code (e.g., in the ThisWorkbook module), it is
using ThisWorkbook.Worksheets instead of
ActiveWorkbook.Worksheets.

Try changing the code to

ActiveWorkbook.Sheets.Add _

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Sh eets.Count)


Beyond that, it all looks good to me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"RB Smissaert" wrote in message
...
Using Excel 97 to 2003.
I have an add-in that somewhere has to add a worksheet after
the last sheet.
This goes with:

ActiveWorkbook.Sheets.Add _
After:=Worksheets(ActiveWorkbook.Sheets.Count)

This goes fine with all the users of the add-in, except one
person, who gets
the error:
Subscript out of range
at this line.

I have tried all kind of manipulations such as deleting,
moving, renaming
and hiding of the sheets, but I just can't reproduce this
error. Protecting
the workbook will cause a different error.
I am just puzzeled what could be causing this error.
Any advice greatly appreciated.


RBS

Subscript out of range when adding worksheet
 

Thanks. I think that indeed the only way this error could happen is if there
were non-worksheet sheets.
This particularly is the case when you count on sheets and then later do
something with worksheets (or vice versa)based on this count. I had got into
the bad habit of intermixing these 2 objects, but they are indeed not the
same.
I have now rewritten to avoid this possible errror and I am still waiting to
hear if this solved it.

RBS

"Tom Ogilvy" wrote in message
...
I duplicated the out of range error using Dicks suggestion of having a non
worksheet sheet (could be hidden). If Chips suggestion doesn't work try:

Sub Tester3()
With ActiveWorkbook
.Sheets.Add _
After:=.Sheets(.Sheets.Count)
End With
End Sub

--
Regards,
Tom Ogilvy

RB Smissaert wrote in message
...
Dick,

Thanks, but there are only normal worksheets.
Have used Chip's suggestion, although I can't really see why it would
solve
the
Subscript out of range error.
Will report back on this.

Bart


"Dick Kusleika" wrote in
message
...
RBS

Are there any non-worksheet sheets (charts, macros) in the workbook?

Change the after arg to

After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets .Count)

Using Sheets.Count as the Item identifier of Worksheets may be where
the
problem is. Alternatively you may be able to use

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Wo rksheets.Count)

But that may not put the new worksheet at the end of *all* sheets.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"RB Smissaert" wrote in message
...
Chip,

Thanks.
I thought that Sheets always defaulted to the active workbook,
unless
specified differently.
But I was indeed worried about this and that is why I did
ActiveWorkbook.Sheets.Count
So you are right I should be consequent and specify the collection
of
sheets
to add to as well.
Strange though that this error only happens with one person.
Will change it and come back to tell what happened.

RBS


"Chip Pearson" wrote in message
...
The only thing that seems like it might be amiss is that you are
not qualifying the Worksheets collection. Depending on the
location of the code (e.g., in the ThisWorkbook module), it is
using ThisWorkbook.Worksheets instead of
ActiveWorkbook.Worksheets.

Try changing the code to

ActiveWorkbook.Sheets.Add _

After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Sh eets.Count)


Beyond that, it all looks good to me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"RB Smissaert" wrote in message
...
Using Excel 97 to 2003.
I have an add-in that somewhere has to add a worksheet after
the last sheet.
This goes with:

ActiveWorkbook.Sheets.Add _
After:=Worksheets(ActiveWorkbook.Sheets.Count)

This goes fine with all the users of the add-in, except one
person, who gets
the error:
Subscript out of range
at this line.

I have tried all kind of manipulations such as deleting,
moving, renaming
and hiding of the sheets, but I just can't reproduce this
error. Protecting
the workbook will cause a different error.
I am just puzzeled what could be causing this error.
Any advice greatly appreciated.


RBS