excel VBA自动化 - 固定格式报表的自动处理

目录

目录

一、录制宏的基础操作

1  新建sheet

2 打开、保存和查看方式

3 关闭屏幕刷新

4 使用相对引用

 5 设置粘贴为数值的快捷键

二、实行for 循环

三、练习案例

练习1 - FOR 循环

练习2 - vlookup自动化

---

一、录制宏的基础操作

1  新建sheet

运行数据录制时，需要新建专用的sheet并重命名，使系统下次运行时可识别正确。

2 打开、保存和查看方式

打开方式：

① 开发工具 - 录制宏

② 右键sheet - 查看代码

③ 可在excel打开宏之后，按F1-查看帮助，打开microsoft运行宏的说明网址：运行宏-说明

   保存方式：带有VBA代码的文件，都将文件另保存为    .xlsm     的格式

 查看代码：开发工具 - visual basic → 模块 

3 关闭屏幕刷新

• 在代码首行下面 输入 excel.Applicantion.ScreenUpdating = FALSE
• 在最下面的end sub前输入 excel.Applicantion.ScreenUpdating = TRUE

即可关闭显示刷新的运行过程,不会再有屏幕刷新的效果

4 使用相对引用

① 绝对引用 ： 无论选中哪个单元格，宏都只会执行录制时所操作的单元格 （录制时选择）

② 相对引用 ：选中某个单元格后，执行宏时，会以被选中的单元格位置作为基准。

 5 设置粘贴为数值的快捷键

① 操作流程

• 开发工具 - 宏 -输入名称 选择性粘贴数值或文本
• 打开代码，输入以下代码

Sub pub_选择性粘贴数值或文本()


' 数值及数字格式

' 快捷键: Ctrl+e

    On Error Resume Next       

    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

    ActiveSheet.PasteSpecial Format:="文本"


End Sub

• 点击 （选项） - 设置快捷键 Ctrl + e

② 代码含义

•  On Error Resume Next     

        如出现运行时错误，程序会继续运行，不中断

•     Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats  

          选择粘贴为数值或文本格式

•     ActiveSheet.PasteSpecial Format:="文本"

          运行中的工作簿粘贴的格式为：文本

二、实行for 循环

目的是让宏自动执行，相对应的运行次数：

• 以另其自动执行9次为例，在第三行输入  For i = 1 To 9

• 随后在end sub 之前，加入   Next

 即可使系统自动执行9次代码

三、练习案例

练习1 - FOR 循环

以下图待待拆分的表格，需将其每行内容拆分，形成8个包含相同表头的独立表。

省份	客户	是否终止合作
福建	AAA	否
江苏	BBB	否
福建	ccc	否
江苏	ddd	否
新疆	eee	否
陕西	fff	否
北京	ggg	否
广东	hhh	否

• 点击录制宏 → 选中表头最左上的单元格 - 使用绝对引用 →  在第一行内容下插入两行空白行 → 复制表头，粘贴在原表的第二行上方 → 将刚粘贴的表头的最前单元格选中 → 停止录制 

• 点击【开发工具 - 宏】，查看到录制好的宏 → 可点击【执行】查看录制效果，单次运行效果如下图

• 点 编辑 → 打开代码界面 因为要形成8个独立的表，意味着代码要循环运行8次
• 在开头输入  For i = 1 To 8   
• 并结尾输入  next

• 最后的运行效果如下图

练习2 - vlookup自动化

• 以日常工作中的vlookup为例，先把报表需要的数据源先整理好 → 放到对应新建的sheet里 
• 做好报表的基本格式 → 点击 录制宏 
• 像平常一样，该VLOOKUP就VLOOKUP → 点停止录制 → 执行宏
• 可关闭屏幕刷新，在开头加 excel.Applicantion.ScreenUpdating = FALSE
• 最下面的end sub前 excel.Applicantion.ScreenUpdating = TRUE