如何创建新增工作表时自动填充的Excel工作表名称列表及下拉菜单？

一、创建自动更新的工作表名称列表 #

公式法（适配Excel 365/2021） #

不用写代码就能生成动态列表：

• 按Ctrl+F3打开名称管理器，新建名为SheetNames的名称
• 引用位置粘贴公式：

  =MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,255)
  

• 返回工作表，在任意空白列（比如A列）输入=SheetNames，Excel会自动溢出所有工作表名称；旧版本可输入=INDEX(SheetNames,ROW(A1))后下拉填充到出现错误为止。

VBA法（全版本通用，更稳定） #

新增工作表时自动触发更新，步骤如下：

1. 按Alt+F11打开VBA编辑器
2. 插入标准模块，粘贴更新列表的代码：

   Sub UpdateSheetList()
       Dim ws As Worksheet
       Dim listWs As Worksheet
       Dim lastRow As Long
       
       ' 指定存放列表的工作表（比如叫"SheetList"）
       Set listWs = ThisWorkbook.Worksheets("SheetList")
       
       ' 清空旧列表
       listWs.Range("A1:A" & listWs.Cells(listWs.Rows.Count, "A").End(xlUp).Row).ClearContents
       
       ' 遍历所有工作表写入名称（排除列表自身）
       lastRow = 1
       For Each ws In ThisWorkbook.Worksheets
           If ws.Name <> listWs.Name Then
               listWs.Cells(lastRow, "A").Value = ws.Name
               lastRow = lastRow + 1
           End If
       Next ws
   End Sub
   

3. 双击左侧ThisWorkbook，选择Workbook和NewSheet事件，粘贴触发代码：

   Private Sub Workbook_NewSheet(ByVal Sh As Object)
       UpdateSheetList
   End Sub
   

4. 保存文件为.xlsm格式（启用宏）

二、创建下拉菜单 #

用动态列表做数据验证：

• 选中要放下拉菜单的单元格（比如Sheet1的B1）
• 点击「数据」→「数据验证」，允许类型选「序列」
• 来源选择存放工作表名的范围（比如SheetList!$A:$A），勾选「提供下拉箭头」后确定

三、基于下拉菜单插入新工作表 #

写宏绑定按钮实现插入功能：

1. 在VBA模块中粘贴插入代码：

   Sub InsertSheetAfterSelected()
       Dim targetSheetName As String
       Dim targetWs As Worksheet
       Dim newWs As Worksheet
       
       ' 获取下拉菜单的值（假设在Sheet1的B1）
       targetSheetName = ThisWorkbook.Worksheets("Sheet1").Range("B1").Value
       
       ' 检查目标工作表是否存在
       On Error Resume Next
       Set targetWs = ThisWorkbook.Worksheets(targetSheetName)
       On Error GoTo 0
       
       If targetWs Is Nothing Then
           MsgBox "指定的工作表不存在！", vbExclamation
           Exit Sub
       End If
       
       ' 在目标工作表后插入新表并命名
       Set newWs = ThisWorkbook.Worksheets.Add(After:=targetWs)
       newWs.Name = InputBox("请输入新工作表名称：", "命名新表", "新工作表")
   End Sub
   

2. 回到Excel，点击「开发工具」→「插入」→「按钮（表单控件）」，关联InsertSheetAfterSelected宏
3. 点击按钮就能根据下拉选择的位置插入新工作表

内容的提问来源于stack exchange，提问作者Automation Monkey