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

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?

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

Kindly use this one liner:

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

ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "XYZ"

(when you add a worksheet, anyway it'll be the active sheet)

Try this:

Public Enum iSide
iBefore
iAfter
End Enum
Private Function addSheet(ByRef inWB As Workbook, ByVal inBeforeOrAfter As iSide, ByRef inNamePrefix As String, ByVal inName As String) As Worksheet
	On Error GoTo the_dark
	
	Dim wsSheet As Worksheet
	Dim bFoundWS As Boolean
	bFoundWS = False
	If inNamePrefix <> "" Then
		Set wsSheet = findWS(inWB, inNamePrefix, bFoundWS)
	End If
	
	If inBeforeOrAfter = iAfter Then
		If wsSheet Is Nothing Or bFoundWS = False Then
			Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = inName
		Else
			Worksheets.Add(After:=wsSheet).Name = inName
		End If
	Else
		If wsSheet Is Nothing Or bFoundWS = False Then
			Worksheets.Add(Before:=Worksheets(1)).Name = inName
		Else
			Worksheets.Add(Before:=wsSheet).Name = inName
		End If
	End If
	
	Set addSheet = findWS(inWB, inName, bFoundWS)         ' just to confirm it exists and gets it handle
	
	the_light:
	Exit Function
	the_dark:
	MsgBox "addSheet: " & inName & ": " & Err.Description, vbOKOnly, "unexpected error"
	Err.Clear
	GoTo the_light
End Function

Try to use:

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"

If you want to check whether a sheet with the same name already exists, you can create a function:

Function funcCreateList(argCreateList)
    For Each Worksheet In ThisWorkbook.Worksheets
        If argCreateList = Worksheet.Name Then
            Exit Function ' if found - exit function
        End If
    Next Worksheet
    Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = argCreateList
End Function

When the function is created, you can call it from your main Sub, e.g.:

Sub main

    funcCreateList "MySheet"

Exit Sub

Try switching the order of your code. You must create the worksheet first in order to name it.

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

thanks,

This will give you the option to:

1. Overwrite or Preserve a tab that has the same name.
2. Place the sheet at End of all tabs or Next to the current tab.
3. Select your New sheet or the Active one.

---

Call CreateWorksheet("New", False, False, False)


Sub CreateWorksheet(sheetName, preserveOldSheet, isLastSheet, selectActiveSheet)
  activeSheetNumber = Sheets(ActiveSheet.Name).Index
  
  If (Evaluate("ISREF('" & sheetName & "'!A1)")) Then 'Does sheet exist?
    If (preserveOldSheet) Then
      MsgBox ("Can not create sheet " + sheetName + ". This sheet exist.")
      Exit Sub
    End If
      Application.DisplayAlerts = False
      Worksheets(sheetName).Delete
    End If
    
    If (isLastSheet) Then
      Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheetName 'Place sheet at the end.
    Else 'Place sheet after the active sheet.
      Sheets.Add(After:=Sheets(activeSheetNumber)).Name = sheetName
    End If
    
    If (selectActiveSheet) Then
      Sheets(activeSheetNumber).Activate
    End If
       
End Sub

This is a quick and simple add of a named tab to the current worksheet:

Sheets.Add.Name = "Tempo"