Naming a newly inserted sheet using VBA

I want to insert a new sheet and name it "ABC":

Sheets.Add
Sheets("Sheet1").Name = "ABC"

This is fine for the first time as it inserts a new sheet called "Sheet1".
However the next time I do it the newly inserted sheet is called "Sheet2",
but the code is still looking for a "Sheet1".

Can I do something simple like:

Sheets.Add.Name = "ABC"

Thanks in advance.

What happened when you tried it?

(It worked for me.)

Ant wrote:
>
> I want to insert a new sheet and name it "ABC":
>
> Sheets.Add
> Sheets("Sheet1").Name = "ABC"
>
> This is fine for the first time as it inserts a new sheet called "Sheet1".
> However the next time I do it the newly inserted sheet is called "Sheet2",
> but the code is still looking for a "Sheet1".
>
> Can I do something simple like:
>
> Sheets.Add.Name = "ABC"
>
> Thanks in advance.

--

Dave Peterson

Yes it does work the first time, but Excel automatically calls the second
sheet "Sheet2" in the workbook so it will debug as it will be looking for
"Sheet1". Actually I have just tried the following and it seemed to work...

Sheets.Add
Dim oSheet As Worksheet
Dim sName As String
sName = "ABC"
Set oSheet = Application.ActiveSheet
oSheet.Name = sName

"Dave Peterson" wrote:

> What happened when you tried it?
>
> (It worked for me.)
>
> Ant wrote:
> >
> > I want to insert a new sheet and name it "ABC":
> >
> > Sheets.Add
> > Sheets("Sheet1").Name = "ABC"
> >
> > This is fine for the first time as it inserts a new sheet called "Sheet1".
> > However the next time I do it the newly inserted sheet is called "Sheet2",
> > but the code is still looking for a "Sheet1".
> >
> > Can I do something simple like:
> >
> > Sheets.Add.Name = "ABC"
> >
> > Thanks in advance.
>
> --
>
> Dave Peterson
>