Is This A Bug????
 

TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a [Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is to us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA

Is This A Bug????
 

If by Bug you mean Excel/VBA - no, if you mean a bug in your code - yes.

change
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)
to
TradeWB.Sheets.Add After:=TradeWB.Worksheets(TradeWB.Sheets.Count)

In passing, why not add 'Worksheets' (but retain Sheets.Count)

Regards,
Peter T

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a [Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA

Is This A Bug????
 

Although I would use Worksheets rather than Sheets as Gary did, that code
worked fine for me adding 20 worksheets.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a [Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA

Is This A Bug????
 

"Peter T" <peter_t@discussions wrote in message
...
If by Bug you mean Excel/VBA - no, if you mean a bug in your code - yes.

change
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)
to
TradeWB.Sheets.Add After:=TradeWB.Worksheets(TradeWB.Sheets.Count)

In passing, why not add 'Worksheets' (but retain Sheets.Count)

Regards,
Peter T

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a [Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA




Thanks for the correction of my code.

Can you explain why my code works for the first 16th iterations but falls
over on the 17th.

I do not understand your "In passing comment", perhaps you could spare few
extra words to allow me to understand and of course answer.

EA

Is This A Bug????
 

"EA" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
If by Bug you mean Excel/VBA - no, if you mean a bug in your code - yes.

change
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)
to
TradeWB.Sheets.Add After:=TradeWB.Worksheets(TradeWB.Sheets.Count)

In passing, why not add 'Worksheets' (but retain Sheets.Count)

Regards,
Peter T

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do
is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a
[Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is
to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA




Thanks for the correction of my code.

Can you explain why my code works for the first 16th iterations but falls
over on the 17th.

this bit -
Worksheets(TradeWB.Sheets.Count)

An unqualified ref to Worksheets 'implicitly' references the Active
workbook. As you are adding sheets to another wb (guess Bob & Gary missed
that in your OP <g) it will fail as soon as 'TradeWB.Sheets.Count' exceeds
the number of sheets in the activeworkbook, so I assume yours had 16 sheets
if it failed adding the 17th.

I do not understand your "In passing comment", perhaps you could spare few

extra words to allow me to understand and of course answer.

Adding 'Sheets' defaults to adding Worksheets. Although it works better
specify Worksheet or Chart(-sheet), more so if you might be doing other
things with a ref to the added worksheet.

Regards,
Peter T

Is This A Bug????
 

What are the newly created sheets named? What is the name of the last sheet
when the code blows up? Do you have a Sheet.xlt file in XLStart that is
used as a template for new sheets?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"EA" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
If by Bug you mean Excel/VBA - no, if you mean a bug in your code - yes.

change
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)
to
TradeWB.Sheets.Add After:=TradeWB.Worksheets(TradeWB.Sheets.Count)

In passing, why not add 'Worksheets' (but retain Sheets.Count)

Regards,
Peter T

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do
is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a
[Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA




Thanks for the correction of my code.

Can you explain why my code works for the first 16th iterations but falls
over on the 17th.

I do not understand your "In passing comment", perhaps you could spare few
extra words to allow me to understand and of course answer.

EA

Is This A Bug????
 

I am not using a Sheet.xlt file in XLSTART.

I am renaming the new sheets after they are added. However I wondered if
the name was creating a problem so I changed the order - result still an
issue when the 17th sheet was attempted to be added.

To me it is still a mystery why the 17th iteration fails.

EA


"Chip Pearson" wrote in message
...
What are the newly created sheets named? What is the name of the last
sheet when the code blows up? Do you have a Sheet.xlt file in XLStart
that is used as a template for new sheets?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"EA" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
If by Bug you mean Excel/VBA - no, if you mean a bug in your code - yes.

change
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)
to
TradeWB.Sheets.Add After:=TradeWB.Worksheets(TradeWB.Sheets.Count)

In passing, why not add 'Worksheets' (but retain Sheets.Count)

Regards,
Peter T

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do
is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a
[Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is
to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA




Thanks for the correction of my code.

Can you explain why my code works for the first 16th iterations but falls
over on the 17th.

I do not understand your "In passing comment", perhaps you could spare
few extra words to allow me to understand and of course answer.

EA

Is This A Bug????
 

Chip

You mentioned having a SHEET.XLT in the XLSTART folder possibly messing up EA's
code.

Using XL 2003

Have a customized SHEET.XLT saved as Template in XLSTART folder.

InsertWorksheet inserts a new sheet based on the SHEET.XLT

But this code does not use the SHEET.XLT as new sheets. Just adds default
sheets

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add.Name = "December " & i
Next
End Sub

This code does use the SHEET.XLT

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add Type:="Worksheet"
Next
End Sub

How to combine so will get named sheets based on SHEET.XLT?

Any ideas?


Gord Dibben

On Sun, 3 Dec 2006 10:12:44 -0000, "EA" wrote:

I am not using a Sheet.xlt file in XLSTART.

I am renaming the new sheets after they are added. However I wondered if
the name was creating a problem so I changed the order - result still an
issue when the 17th sheet was attempted to be added.

To me it is still a mystery why the 17th iteration fails.

EA


"Chip Pearson" wrote in message
...
What are the newly created sheets named? What is the name of the last
sheet when the code blows up? Do you have a Sheet.xlt file in XLStart
that is used as a template for new sheets?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"EA" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
If by Bug you mean Excel/VBA - no, if you mean a bug in your code - yes.

change
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)
to
TradeWB.Sheets.Add After:=TradeWB.Worksheets(TradeWB.Sheets.Count)

In passing, why not add 'Worksheets' (but retain Sheets.Count)

Regards,
Peter T

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do
is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a
[Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is
to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA




Thanks for the correction of my code.

Can you explain why my code works for the first 16th iterations but falls
over on the 17th.

I do not understand your "In passing comment", perhaps you could spare
few extra words to allow me to understand and of course answer.

EA







Gord Dibben MS Excel MVP

Is This A Bug????
 

Try it this way. (qualify all commands, don't mix worksheets/sheets)

With TradeWB.Worksheets
.Add After:=.Item(.Count)
End With


Sub ABC()
Dim i As Long
Dim TradeWb as Workbook
Set TradeWb = Workbooks("Book2")
For i = 1 To 20
With TradeWb.Worksheets
.Add After:=.Item(.Count)
End With
Next i
End Sub

worked fine for me.

--
Regards,
Tom Ogilvy






"EA" wrote in message
...
I am not using a Sheet.xlt file in XLSTART.

I am renaming the new sheets after they are added. However I wondered if
the name was creating a problem so I changed the order - result still an
issue when the 17th sheet was attempted to be added.

To me it is still a mystery why the 17th iteration fails.

EA


"Chip Pearson" wrote in message
...
What are the newly created sheets named? What is the name of the last
sheet when the code blows up? Do you have a Sheet.xlt file in XLStart
that is used as a template for new sheets?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"EA" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
If by Bug you mean Excel/VBA - no, if you mean a bug in your code -
yes.

change
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)
to
TradeWB.Sheets.Add After:=TradeWB.Worksheets(TradeWB.Sheets.Count)

In passing, why not add 'Worksheets' (but retain Sheets.Count)

Regards,
Peter T

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to do
is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a
[Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is
to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA




Thanks for the correction of my code.

Can you explain why my code works for the first 16th iterations but
falls over on the 17th.

I do not understand your "In passing comment", perhaps you could spare
few extra words to allow me to understand and of course answer.

EA

Is This A Bug????
 

Tom

In this thread I posted a question for Chip but maube you could give me a hand?

Quoted below from original post.........

Chip

You mentioned having a SHEET.XLT in the XLSTART folder possibly messing up EA's
code.

Using XL 2003

Have a customized SHEET.XLT saved as Template in XLSTART folder.

InsertWorksheet inserts a new sheet based on the SHEET.XLT

But this code does not use the SHEET.XLT as new sheets. Just adds default
sheets

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add.Name = "December " & i
Next
End Sub

This code does use the SHEET.XLT

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add Type:="Worksheet"
Next
End Sub

How to combine so will get named sheets based on SHEET.XLT?

Any ideas?


Gord Dibben

On Sun, 3 Dec 2006 15:42:07 -0500, "Tom Ogilvy" wrote:

Try it this way. (qualify all commands, don't mix worksheets/sheets)

With TradeWB.Worksheets
.Add After:=.Item(.Count)
End With


Sub ABC()
Dim i As Long
Dim TradeWb as Workbook
Set TradeWb = Workbooks("Book2")
For i = 1 To 20
With TradeWb.Worksheets
.Add After:=.Item(.Count)
End With
Next i
End Sub

worked fine for me.

Gord Dibben MS Excel MVP

Is This A Bug????
 

You can use Type:= to point at the template file:

Option Explicit
Sub Add_Sheets()
Dim i As Long
For i = 31 To 1 Step -1
Sheets.Add Type:=Application.StartupPath & "\sheet.xlt"
ActiveSheet.Name = "December " & i
Next i
End Sub


Gord Dibben wrote:

Tom

In this thread I posted a question for Chip but maube you could give me a hand?

Quoted below from original post.........

Chip

You mentioned having a SHEET.XLT in the XLSTART folder possibly messing up EA's
code.

Using XL 2003

Have a customized SHEET.XLT saved as Template in XLSTART folder.

InsertWorksheet inserts a new sheet based on the SHEET.XLT

But this code does not use the SHEET.XLT as new sheets. Just adds default
sheets

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add.Name = "December " & i
Next
End Sub

This code does use the SHEET.XLT

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add Type:="Worksheet"
Next
End Sub

How to combine so will get named sheets based on SHEET.XLT?

Any ideas?

Gord Dibben

On Sun, 3 Dec 2006 15:42:07 -0500, "Tom Ogilvy" wrote:

Try it this way. (qualify all commands, don't mix worksheets/sheets)

With TradeWB.Worksheets
.Add After:=.Item(.Count)
End With


Sub ABC()
Dim i As Long
Dim TradeWb as Workbook
Set TradeWb = Workbooks("Book2")
For i = 1 To 20
With TradeWb.Worksheets
.Add After:=.Item(.Count)
End With
Next i
End Sub

worked fine for me.

Gord Dibben MS Excel MVP

--

Dave Peterson

Is This A Bug????
 

Gord,

I've never paid much attention to Sheet.xlt because I don't use one (I do
use Book.xlt though). The following seem to work as expected:

''''''''''''''''''''''''''''''''''
' This uses Sheet.xlt
''''''''''''''''''''''''''''''''''
ThisWorkbook.Sheets.Add(Type:="Worksheet").Name = "NewSheet"

''''''''''''''''''''''''''''''''''
' This does not use Sheet.xlt
''''''''''''''''''''''''''''''''''
ThisWorkbook.Sheets.Add.Name = "NewSheet2"



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)




"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Chip

You mentioned having a SHEET.XLT in the XLSTART folder possibly messing up
EA's
code.

Using XL 2003

Have a customized SHEET.XLT saved as Template in XLSTART folder.

InsertWorksheet inserts a new sheet based on the SHEET.XLT

But this code does not use the SHEET.XLT as new sheets. Just adds default
sheets

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add.Name = "December " & i
Next
End Sub

This code does use the SHEET.XLT

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add Type:="Worksheet"
Next
End Sub

How to combine so will get named sheets based on SHEET.XLT?

Any ideas?


Gord Dibben

On Sun, 3 Dec 2006 10:12:44 -0000, "EA" wrote:

I am not using a Sheet.xlt file in XLSTART.

I am renaming the new sheets after they are added. However I wondered if
the name was creating a problem so I changed the order - result still an
issue when the 17th sheet was attempted to be added.

To me it is still a mystery why the 17th iteration fails.

EA


"Chip Pearson" wrote in message
...
What are the newly created sheets named? What is the name of the last
sheet when the code blows up? Do you have a Sheet.xlt file in XLStart
that is used as a template for new sheets?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"EA" wrote in message
...
"Peter T" <peter_t@discussions wrote in message
...
If by Bug you mean Excel/VBA - no, if you mean a bug in your code -
yes.

change
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)
to
TradeWB.Sheets.Add After:=TradeWB.Worksheets(TradeWB.Sheets.Count)

In passing, why not add 'Worksheets' (but retain Sheets.Count)

Regards,
Peter T

"EA" wrote in message
...
TradeWB.Sheets.Add After:=Worksheets(TradeWB.Sheets.Count)

I am adding worksheets to a WB using the above line. All I want to
do
is
add a new wks after the last wks in the book.

The line of code work for the first 16 iterations but causes a
[Run-time
error '9' Subscript out of range] error on the 17th iteration.

The only way I can get the line to work after adding 16 worksheets is
to
us
a :

TradeWB.activate statement before the above line of code.

I know this is a workaround, but is this a bug?

Can anyone tell me a way of adding worksheets, after the last current
worksheet without having to activate the workbook?

EA




Thanks for the correction of my code.

Can you explain why my code works for the first 16th iterations but
falls
over on the 17th.

I do not understand your "In passing comment", perhaps you could spare
few extra words to allow me to understand and of course answer.

EA







Gord Dibben MS Excel MVP

Is This A Bug????
 

Thanks Chip

I wound up with this and works fine with SHEET.XLT

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add(Type:="Worksheet").Name = "December " & i
Next
End Sub

I thought I had tried that combination but I guess not, or I had some other
syntax error. Maybe had no parens.

More of a learning experience than anything else, but tried to help a poster a
long time past and it has been bugging me.

Gord

On Sun, 3 Dec 2006 16:45:31 -0600, "Chip Pearson" wrote:

Gord,

I've never paid much attention to Sheet.xlt because I don't use one (I do
use Book.xlt though). The following seem to work as expected:

''''''''''''''''''''''''''''''''''
' This uses Sheet.xlt
''''''''''''''''''''''''''''''''''
ThisWorkbook.Sheets.Add(Type:="Worksheet").Name = "NewSheet"

''''''''''''''''''''''''''''''''''
' This does not use Sheet.xlt
''''''''''''''''''''''''''''''''''
ThisWorkbook.Sheets.Add.Name = "NewSheet2"

Gord Dibben MS Excel MVP

Is This A Bug????
 

Thanks Dave

See my response to Chip's post.

Gord

On Sun, 03 Dec 2006 16:37:13 -0600, Dave Peterson
wrote:

You can use Type:= to point at the template file:

Option Explicit
Sub Add_Sheets()
Dim i As Long
For i = 31 To 1 Step -1
Sheets.Add Type:=Application.StartupPath & "\sheet.xlt"
ActiveSheet.Name = "December " & i
Next i
End Sub


Gord Dibben wrote:

Tom

In this thread I posted a question for Chip but maube you could give me a hand?

Quoted below from original post.........

Chip

You mentioned having a SHEET.XLT in the XLSTART folder possibly messing up EA's
code.

Using XL 2003

Have a customized SHEET.XLT saved as Template in XLSTART folder.

InsertWorksheet inserts a new sheet based on the SHEET.XLT

But this code does not use the SHEET.XLT as new sheets. Just adds default
sheets

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add.Name = "December " & i
Next
End Sub

This code does use the SHEET.XLT

Sub Add_Sheets()
For i = 31 To 1 Step -1
Sheets.Add Type:="Worksheet"
Next
End Sub

How to combine so will get named sheets based on SHEET.XLT?

Any ideas?

Gord Dibben

On Sun, 3 Dec 2006 15:42:07 -0500, "Tom Ogilvy" wrote:

Try it this way. (qualify all commands, don't mix worksheets/sheets)

With TradeWB.Worksheets
.Add After:=.Item(.Count)
End With


Sub ABC()
Dim i As Long
Dim TradeWb as Workbook
Set TradeWb = Workbooks("Book2")
For i = 1 To 20
With TradeWb.Worksheets
.Add After:=.Item(.Count)
End With
Next i
End Sub

worked fine for me.

Gord Dibben MS Excel MVP

Gord Dibben MS Excel MVP

Is This A Bug????
 

To me it is still a mystery why the 17th iteration fails.

EA

Did you see the probable explanation in my previous post.

Regards,
Peter T