如何在所有excel表格的最后添加一个指定的表格?

I am trying to Add an Excel sheet with Name "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? thanks

你能告诉我为什么吗?谢谢

8 个解决方案

#1

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

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

#2

Kindly use this one liner:

请使用这一行:

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

#3

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

#4

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

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

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

#5

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

#6

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,

谢谢,

#7

This will give you the option to:

这将给你一个选择:

1. Overwrite or Preserve a tab that has the same name.
2. 覆盖或保存具有相同名称的选项卡。
3. Place the sheet at End of all tabs or Next to the current tab.
4. 将表放在所有标签页的末尾或在当前选项卡的旁边。
5. Select your New sheet or the Active one.
6. 选择您的新表或活动表。

---

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

#8

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

这是对当前工作表的命名选项卡的快速和简单的添加:

Sheets.Add.Name = "Tempo"