vba 如何在所有 Excel 工作表的末尾添加命名工作表？

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

提问by Behseini

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

我试图在所有现有工作表的末尾添加一个名为“Temp”的 Excel 工作表，但此代码不起作用：

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?

你能告诉我为什么吗？

回答by L42

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 Withclause to avoid repeatedly calling out your object

或使用With子句避免重复调用您的对象

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

回答by Amar

Kindly use this one liner:

请使用这个衬垫：

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

回答by Saptarshi

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

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

（当您添加工作表时，无论如何它将成为活动工作表）

回答by Mr F

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

回答by Ivan Tokarev

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 调用它，例如：

Sub main

    funcCreateList "MySheet"

Exit Sub

回答by moberme

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.

1. 覆盖或保留具有相同名称的选项卡。
2. 将工作表放在所有选项卡的末尾或当前选项卡的旁边。
3. 选择您的新工作表或活动工作表。

---

---

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

回答by Developer

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,

谢谢，

回答by Jan

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

这是将命名选项卡快速简单地添加到当前工作表的方法：

Sheets.Add.Name = "Tempo"