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?

1

8 Answers

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
5

Kindly use this one liner:

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "new_sheet_name"
2
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"