夜雨聆风学习资料网

ARTICLE · 1390411

Excel VBA编程-一键批量新建多个工作表(按序号或清单自动命名)

Excel VBA编程-一键批量新建多个工作表(按序号或清单自动命名)

你是不是也遇到过这种事:月底要建 12 张「1 月、2 月……12 月」的表,或者要给 20 个部门各建一张表。点右下角的「+」号一张张加,加了还要改名,手都点酸了,还容易漏、容易错。

其实 Excel 里一句简单的 VBA,就能一键把这些都建好、还顺手把名字起好。今天就教你这一招。

一、今天我们学什么

今天只讲一个方法:Worksheets.Add。

把它拆成一句大白话:

  • Worksheets 就是「当前工作簿里所有的工作表」这个大家庭;
  • Add 的意思是「往这个家庭里加一个新成员(一张新表)」;
  • 加好之后,再用 表的名字属性(Name) 给这张新表起个名字。

为什么要把它单独拎出来讲?因为这里藏着一个新手必踩的坑:你用 Add 加了一张新表之后,新表会自动变成「当前正在看的那张表」。如果你不先把这张新表「抓」到一个变量里,下一步想给它改名时就会找错对象、甚至直接报错。

所以今天记住一句口诀:先 Add 抓到新表,再点 Name 起名字,两步顺序不能反。

网上有不少实战例子(比如在线计算网、TrueSight、DevGex、StackOverflow 上都讲过这个套路),核心都一致:用 Set 新表 = Worksheets.Add(...) 先把新表接住,再 新表.Name = "名字" 命名。下面我们把这个套路扩展成 3 个由浅入深的案例。

下面这张图,帮你把今天的逻辑一次看明白:

【SVG 示意图】

二、案例一(简单):固定新建 5 张表,自动命名为 表1、表2……

功能说明

这个最简单:你只想一键生成 5 张空白表,名字依次是「表1、表2、表3、表4、表5」。代码里把数量写死成 5,不需要你输入任何东西,按一下就建好。

代码

Sub 一键新建5张表()    Dim i As Long    Dim 新表 As Worksheet    For i = 1 To 5        Set 新表 = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))        新表.Name = "表" & i    Next i    MsgBox "已新建 5 张工作表!", vbInformationEnd Sub

操作步骤

  1. 打开 Excel,按 Alt + F11 进入 VBA 编辑器;
  2. 在左侧「工程资源管理器」里,右键你的工作簿,点「插入」→「模块」;
  3. 把上面这段代码整段复制进去;
  4. 把光标放在代码里,按 F5 运行;
  5. 回到 Excel,你会看到工作簿末尾多了 5 张表,名字分别是 表1 到 表5,并弹出一个完成提示。

三、案例二(中等):自己输入数量和前缀,批量建表

功能说明

案例一的数量是写死的。这个案例让你自己决定建几张、叫什么前缀:运行时弹窗问你「建几张」,再弹窗问你「统一前缀写什么」,然后自动生成「前缀1、前缀2……」。比如前缀填「部门」,就得到 部门1、部门2……

代码

Sub 一键按数量与前缀批量建表()    Dim 数量 As Long    Dim 前缀 As String    Dim i As Long    Dim 新表 As Worksheet    数量 = Val(InputBox("要新建几张工作表?请输入数字:", "批量建表"))    前缀 = InputBox("给这些表起个统一前缀(例如:部门、月份):", "批量建表")    If 数量 <= 0 Then        MsgBox "数量必须大于 0,已取消。", vbExclamation        Exit Sub    End If    For i = 1 To 数量        Set 新表 = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))        新表.Name = 前缀 & i    Next i    MsgBox "已新建 " & 数量 & " 张工作表,名字是:" & 前缀 & "1 到 " & 前缀 & 数量 & "!", vbInformationEnd Sub

操作步骤

  1. 同样在模块里粘贴这段代码;
  2. 按 F5 运行,会先弹出一个框,输入想要的数量(比如 8),点确定;
  3. 再弹出一个框,输入统一前缀(比如 月份),点确定;
  4. 工作簿里就会多出 月份1 到 月份8 共 8 张表,并弹出完成提示。
  5. 小提醒:如果数量填 0 或留空,代码会提示「已取消」,不会乱建表。

四、案例三(实用):按 A 列清单,逐张新建并命名

功能说明

这是最实用的场景:你手里有一份名字清单(比如 20 个客户名、12 个月份名),不想一张张建。做法:把清单放在第 1 张表的 A 列(第 1 行写表头如「表名」,从第 2 行起每个格子写一个名字),运行代码后,程序会读清单、逐张新建工作表,并以清单里的名字命名,最后告诉你成功建了几张。

代码

Sub 一键按清单批量建表()    Dim 清单表 As Worksheet    Dim 末行 As Long    Dim i As Long    Dim 表名 As String    Dim 新表 As Worksheet    Dim 成功数 As Long    ' 第 1 张表放名字清单,从第 2 行开始(第 1 行是表头)    Set 清单表 = ActiveWorkbook.Worksheets(1)    末行 = 清单表.Cells(清单表.Rows.Count, 1).End(xlUp).Row    For i = 2 To 末行        表名 = 清单表.Cells(i, 1).Value        If 表名 <> "" Then            Set 新表 = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))            新表.Name = 表名            成功数 = 成功数 + 1        End If    Next i    MsgBox "已根据清单新建 " & 成功数 & " 张工作表!", vbInformationEnd Sub

操作步骤

  1. 在 Excel 里,确保第 1 张工作表的 A 列是名字清单:A1 写表头(如「表名」),A2、A3…… 每行一个名字;
  2. 把这段代码粘贴到模块里;
  3. 按 F5 运行;
  4. 程序会从第 2 行开始往下读,每读到一个名字就新建一张表并改名,遇到空行自动跳过;
  5. 全部建完后弹出「已根据清单新建 N 张工作表」的提示。

五、避坑提醒(很重要)

建表最容易出错的就是名字。在清单里写名字前,请务必记住这三条铁律(和前面几篇讲表名时一样):

  1. 名字最长 31 个字符(一个汉字算一个字符),超长会报错;
  2. 名字里不能包含这些符号:\ / ? * [ ] :,用了就报错;
  3. 名字不能和已有工作表重名,重复了也会报错。

另外再强调一次今天的专属坑:一定要先 Set 新表 = ...Add(...) 把新表接住,再 新表.Name = ... 改名。如果反过来,先写 新表.Name 再 Set 新表 = Add,VBA 会因为「新表」还没指向任何真实表格而直接报错。

如果你的清单里有不合规的名字,运行到那一行会停下来报错——这时只要按上面三条检查一下那个名字、改好再重新运行即可(已建好的表不会消失)。

六、知识点小结

  • 今天只学了一个方法:Worksheets.Add,作用是在工作簿里新增一张工作表;
  • 正确姿势是「先 Add 接住新表,再点 Name 起名」,顺序不能反;
  • 用 After:=Worksheets(Worksheets.Count) 可以把新表加在最后,保持顺序整齐;
  • 配合 For...Next 循环,就能一次建很多张;配合 InputBox 能让数量、前缀随时由你定;配合「读第 1 张表 A 列清单」能按名单建表;
  • 表名三铁律:不超 31 字、不含 \ / ? * [ ] :、不能重名。

记住口诀:Add 抓新表,Name 起名字,循环一次性搞定。 下一篇我们聊聊怎么把建好的表再快速整理、排版,让它们整整齐齐。

往期精选

  • Excel VBA编程-一键批量给工作表名称加上统一前缀或后缀
  • Excel VBA编程-一键批量按清单重命名工作表(让表名一目了然)
  • Excel VBA编程-一键自动调整行高和列宽(按内容自适应,AutoFit)
  • Excel VBA编程-一键批量设置行高和列宽(让表格更整齐)
  • Excel VBA编程-一键批量隐藏或显示指定的行(让报表更清爽)
  • Excel VBA编程-一键批量清除批注(删除多余评论)
  • Excel VBA编程-一键给单元格批量添加批注(加评论说明)

作者:西哥的AI幻觉 | 公众号:西哥的AI幻觉 本文由 VBA 零基础系列教程自动整理发布,代码均可直接复制运行。

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料