Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of Range | Excel Discussion (Misc queries) | |||
what does 'Subscript Out of range' mean?? | Excel Worksheet Functions | |||
9: Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript our of range when activating a worksheet | Excel Programming | |||
Subscript Out of Range | Excel Programming |