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

试试这个:

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

或者使用 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

如果您不需要在代码的其余部分调用相同的工作表，那么上面的代码可以进一步简化。

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

试试这个:

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

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

(当您添加工作表时，无论如何它将是活动工作表)

这是在当前工作表中快速简单地添加一个命名选项卡:

Sheets.Add.Name = "Tempo"

请使用下面这句话:

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

尝试使用:

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

如果要检查具有相同名称的工作表是否已经存在，可以创建一个函数:

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

创建函数时，可以从主 Sub 调用它，例如:

Sub main


funcCreateList "MySheet"


Exit Sub

尝试切换代码的顺序。必须先创建工作表才能命名它。

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

谢谢,

这样你就可以选择:

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