Subscript out of range when adding worksheet

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
R

RB Smissaert

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
 
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.Sheets.Count)


Beyond that, it all looks good to me.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
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
 
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.Worksheets.Count)

But that may not put the new worksheet at the end of *all* sheets.
 
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
 
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
 
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
 
Back
Top