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

This will allow you to choose whether to overwrite or save a tab with the same name.
Place the sheet at the end of all tabs or right next to the one you're working on.
Choose from the New and Active sheets.

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

Answer 2

Kindly use this one liner:

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

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