一、前言

SUT学习交流群中有位小伙伴从网上找了一个Excel模板,该模板我倒不感兴趣,但我对该模板怎么生成的比较感兴趣,因为该模板默认生成了100个对应序号的工作表,点击【客户总览】工作表对应的序号,就会跳转到对应的工作表。如果【客户总览】工作表序号对应的其他列信息填写了相应的内容,那跳转对应的工作表也填写相应的内容。是不是很抽象,听不懂我在说啥?没关系,我们来一起就是见如下动态图:

演示模板效果

不知道我上面的动态图一顿操作,大伙有没有看懂呢?没看懂也没关系,因为文件以及放到了QQ群中,可以下载下来仔细研读。

二、扩展模板

通过上面的动态图可以看到,目前的模板工作表超链接受限于只有100个,如果希望把超链接工作表增加到500或者1000,那需要怎么做呢?手动做?如果你有的是时间的话可以考虑。毕竟每一个链接的工作表内容还需要衔接到第一个【客户总览】工作表序号列。为了批量扩展模板,这里比较方便的方法就是使用VBA写一段代码。具体思路如下:

1、找到起始序号

通过肉眼直观的来看【客户总览】工作表,可以看到目前序号列最大数是100,位于102行。因为数据是连续的,所以我们不需要使用Do While……Loop来查找,当然这样也没有错误,就是效率低了点。

Dim iNum As Integer
Worksheets("客户总览").Select
Range("A3").Select
Do While ActiveCell.Value <> ""
    ActiveCell.Offset(1, 0).Select
Loop
iNum = ActiveCell.Row

因为前面有前提,数据是连续的,所以可以使用单元格CurrentRegion对象中的Rows的Count属性来获取行数。

Dim iNum As Integer
iNum = Worksheets("客户总览").Range("A1").CurrentRegion.Rows.Count + 1

上面的代码之所以要加1,是由于我们需要找到序列列中有值的下一行,从此处递增序号。

2、递增序号、居中显示和设置超链接

序列这一列最大值是100,我们需要生成到500或者1000,需要对其进行递增,这里可以使用For……Next循环来实现,顺带设置一下生成序号的居中问题。

For i = iNum To 500 + 2
   '设置序号、格式和超链接
   Cells(i, 1).Value = i - 2
   Cells(i, 1).HorizontalAlignment = xlCenter
   ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:="", SubAddress:=(i - 2) & "!A1"
Next

3、复制工作表并重命名为对应序号

因为模板文件格式雷同,除了里面的公式不一样外,其他没啥区别。所以我们可以复制最后一个工作表,这个案例里面就是名称为100这个工作表。复制成功后,修改其名称为101,以此类推。

'复制工作表并重命名
Sheets(Worksheets.Count).Copy After:=Sheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = i - 2

上面代码中的i – 2是由于序号行数和要生成的工作表名称本来就相差了2(序列是100,所在行是102),所以需要减去。

4、对重命名后的工作表单元格设置公式

复制出来的工作表肯定都和前一个工作表一模一样,那里面的公式也就相应的不对了,需要对应对其设置递增或者替换。

'批量修改公式
For j = 0 To UBound(sutArray)
    Sheets(Worksheets.Count).Range(sutArray(j)).Formula = Replace(Sheets(Worksheets.Count).Range(sutArray(j)).Formula, i - 1, i)
Next

这里面用到了一个一维数组sutArray,这个是啥呢?其实就是需要替换的单元格集合。具体定义和赋值如下:

Dim sutArray
sutArray = Array("A1", "B2", "B3", "B4", "B5", "D1", "D2", "D3", "D4", "D5")

5、定位到【客户总览】工作表

到这里,大伙是不是觉得已经完事了?其实不然,因为此时还定位在前面新复制的工作表里,我们需要再次定位到【客户总览】工作表。

Worksheets("客户总览").Select

三、最终演示

把上面的代码稍微罗列一下,放到一个Sub过程里面,就可以运行了,我们一起来看看运行效果吧。这里生成50个工作表为例。

演示代码运行

不要问我为啥输入SUT后电脑卡住不动(大伙可以赞助买台好电脑,这样就不会卡了)。其实代码屏幕刷新最好关掉,有利于提高代码执行效率。代码的开头设置Application.ScreenUpdating = False,代码的结尾设置Application.ScreenUpdating = True即可。

四、我的总结

其实到这里也没太多需要总结的,因为代码已经分开讲解了,有点VBA代码基础的肯定都能看得懂的。不过大伙在编写代码过程中要尽可能的做到代码规范和提高 效率。虽然今天介绍的代码里很少,也不会执行太长时间,但如果批量生成上千个工作表,细微的效率提升,会节约很多时间哦。希望大伙都能SaveUTime – 节约您的时间!

本文由 Steven 创作,可自由转载、引用,但需署名作者且注明出处。