Worksheet name
 

Excel Experts,

I have a workbook which has a sheet named "BskTrades". I want my code to
add a new sheet after BskTrades and name it "BskTotals".

My code adds a new sheets after BskTrades but fails on the second line which
I want to name the new sheet "BskTotals".

What do I have wrong?

Sub AddSheet()

Sheets.Add After:=Worksheets("BskTrades")

Worksheets(Worksheets("BskTrades").Count + 1).Name = "BskTotals"

End Sub

Thanks in advance,
Alan

--
achidsey

Worksheet name
 

When you add a sheet by default it becomes the active sheet. YOu can just use
code like this...

Sheets.Add After:=Worksheets("BskTrades")
ActiveSheet.Name = "BskTotals"

You should however have some error handling on this code. If a sheet by that
name exists, then the code will crash when you try to rename.
--
HTH...

Jim Thomlinson


"achidsey" wrote:


Excel Experts,

I have a workbook which has a sheet named "BskTrades". I want my code to
add a new sheet after BskTrades and name it "BskTotals".

My code adds a new sheets after BskTrades but fails on the second line which
I want to name the new sheet "BskTotals".

What do I have wrong?

Sub AddSheet()

Sheets.Add After:=Worksheets("BskTrades")

Worksheets(Worksheets("BskTrades").Count + 1).Name = "BskTotals"

End Sub

Thanks in advance,
Alan

--
achidsey

Worksheet name
 

not sure if this is the best way, but works for me

Sub AddSheet()

With Worksheets("BskTrades")
Sheets.Add After:=Worksheets("BskTrades")
End With
ActiveSheet.Name = "BskTotals"

End Sub


--


Gary


"achidsey" (notmorespam) wrote in message
...

Excel Experts,

I have a workbook which has a sheet named "BskTrades". I want my code to
add a new sheet after BskTrades and name it "BskTotals".

My code adds a new sheets after BskTrades but fails on the second line
which
I want to name the new sheet "BskTotals".

What do I have wrong?

Sub AddSheet()

Sheets.Add After:=Worksheets("BskTrades")

Worksheets(Worksheets("BskTrades").Count + 1).Name = "BskTotals"

End Sub

Thanks in advance,
Alan

--
achidsey

Worksheet name
 

An individual worksheet (Worksheets("BskTrades")) doesn't have a .Count
property. If "BskTrades" is ALWAYS the last worksheet in the workbook,
you could use

Sheets.Add After:=Worksheets("BskTrades")
Worksheets(Worksheets.Count).Name = "BskTotals"

But you're better off using a object variable (either declared

Dim ws As Worksheet
Set ws = Worksheets.Add(After:=Worksheets("BskTrades"))
ws.Name = "BskTotal"

or temporary, using With...End With):

With Sheets.Add(After:=Worksheets("BskTrades"))
.Name = "BskTotal"
End With

or you could do it in one go:

Sheets.Add(After:=Worksheets("BskTrades")).Name = "BskTotal"

In article ,
"achidsey" (notmorespam) wrote:

Excel Experts,

I have a workbook which has a sheet named "BskTrades". I want my code to
add a new sheet after BskTrades and name it "BskTotals".

My code adds a new sheets after BskTrades but fails on the second line which
I want to name the new sheet "BskTotals".

What do I have wrong?

Sub AddSheet()

Sheets.Add After:=Worksheets("BskTrades")

Worksheets(Worksheets("BskTrades").Count + 1).Name = "BskTotals"

End Sub

Thanks in advance,
Alan

Worksheet name
 

Jim and Gary,

Thanks much for the assistance.

Alan

--
achidsey


"achidsey" wrote:


Excel Experts,

I have a workbook which has a sheet named "BskTrades". I want my code to
add a new sheet after BskTrades and name it "BskTotals".

My code adds a new sheets after BskTrades but fails on the second line which
I want to name the new sheet "BskTotals".

What do I have wrong?

Sub AddSheet()

Sheets.Add After:=Worksheets("BskTrades")

Worksheets(Worksheets("BskTrades").Count + 1).Name = "BskTotals"

End Sub

Thanks in advance,
Alan

--
achidsey