creating a sheet at the end of the file

[kenguru]

Hello,

I have an excel file with 7 sheets. I would like to write a code for creating a sheet at the end of the file (number 8).
I wrote the following:
Dim xlSheet As Excel.Worksheet
Set xlSheet = Worksheets.Add(,"Service")
xlSheet.Name = "My Sheet"

I wrote Service, because the 7th sheet name is "Service", and i wanted to put the new sheet after this. But it didn't work out for me.

I managed to do it, in the following way:
Worksheets.Add Before:=Worksheets("Sheet3")
activesheet.Name = "My Sheet"
But i would like to do it with my first method.

Any suggestions?
Thank you.

 

[PHV]

Like this ?
Set xlSheet = Worksheets.Add(, Worksheets("Service"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kenguru]

And how could i insert the sheet only if it not exist?

Thank you.
Kenguru

 

[Bong]

addsheet=1
foreach s in worksheets
if s.name="mySheet" then addsheet=0
next
if addsheet then worksheets.add ...

_________________
Bob Rashkin

 

[GlennUK]

Or ...

    On Error Resume Next
    If IsError(Sheets("My sheet")) Then
        Set xlSheet = Worksheets.Add(, Worksheets("Service"))
    End If
    On Error GoTo 0

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Rofeu]

Hi,

if you want to add a sheet to the end of the file (no matter the name of the last sheet) use this construction:

With ThisWorkbook
    .Sheets.Add After:=.Sheets(.Sheets.Count)
End With

Cheers,

Roel