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

8 回答

• 5

  尝试使用：

  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
  

  回复于 2024-04-19T18:43:59+08:00
• 5

  尝试切换代码的顺序 . 您必须首先创建工作表才能为其命名 .

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

  谢谢，

  回复于 2024-04-19T18:43:59+08:00
• 109

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

  （当你添加一个工作表时，无论如何它都是活动表）

  回复于 2024-04-19T18:43:59+08:00
• 1

  请使用这一个班轮：

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

  回复于 2024-04-19T18:43:59+08:00
• -3

  这是一个快速简单的命名选项卡添加到当前工作表：

  Sheets.Add.Name = "Tempo"
  

  回复于 2024-04-19T18:43:59+08:00
• 27

  试试这个：

  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
  

  回复于 2024-04-19T18:43:59+08:00
• 2

  这将为您提供以下选项：

  • 覆盖或保留具有相同名称的选项卡 .

  • 将工作表放在所有选项卡的末尾或当前选项卡的旁边 .

  • 选择新工作表或活动工作表 .

  ---

  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
  

  回复于 2024-04-19T18:43:59+08:00
• 1

  试试这个：

  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
  

  回复于 2024-04-19T18:43:59+08:00