How to add a named sheet at the end of all Excel sheets?

Question

I am trying to add an Excel sheet named "Temp" at the end of all existing sheets, but this code is not working:

Private Sub CreateSheet()     Dim ws As Worksheet     ws.Name = "Tempo"     Set ws = Sheets.Add(After:=Sheets(Sheets.Count)) End Sub 

Can you please let me know why?

Answer 1

Try this:

Private Sub CreateSheet()     Dim ws As Worksheet     Set ws = ThisWorkbook.Sheets.Add(After:= _              ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))     ws.Name = "Tempo" End Sub 

Or use a With clause to avoid repeatedly calling out your object

Private Sub CreateSheet()     Dim ws As Worksheet     With ThisWorkbook         Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))         ws.Name = "Tempo"     End With End Sub 

Above can be further simplified if you don't need to call out on the same worksheet in the rest of the code.

Sub CreateSheet()     With ThisWorkbook         .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"     End With End Sub 

Answer 2

Kindly use this one liner:

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "new_sheet_name"