参考:
https://www.cnblogs.com/hackxiyu/p/8945975.html
场景:将多个sheet表拆分成独立的excel文件
一、安装VB工具:
默认情况下:wps -- 开发工具 --- VB 编辑器是灰选状态(即不可用状态),此时需要先安装vb工具:VBA For WPS 2019
安装步骤依次为:vcredist_x86.exe、Vba71.msi、Vba71_1033.MSI、Vba71_2052.msi
二、操作步骤:
点击 【开发工具】--》【vba编辑器】--》【插入】--》【模块】,输入如下代码:
Private Sub 分拆工作表()
Dim sht As Worksheet
Dim MyBook As Workbook
Set MyBook = ActiveWorkbook
For Each sht In MyBook.Sheets
sht.Copy
ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & sht.Name, FileFormat:=xlNormal '将工作簿另存为EXCEL默认格式
ActiveWorkbook.Close
Next
MsgBox "文件已经被分拆完毕!"
End Sub
上述代码粘贴后,不用保存,直接点击运行即可拆分文件到该报文的同级目录下,并以sheet的名字命名新文件
源表:
最后效果:
创建文件夹,然后保存文件
Option Explicit Option Compare Text Private Sub 分拆工作表() Dim sht As Worksheet Dim MyBook As Workbook Set MyBook = ActiveWorkbook For Each sht In MyBook.Sheets sht.Copy On Error Resume Next VBA.MkDir (MyBook.Path & "\" & sht.Name) ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & sht.Name & "\" & sht.Name, FileFormat:=xlNormal '将工作簿另存为EXCEL默认格式 ActiveWorkbook.Close Next MsgBox "文件已经被分拆完毕!" End Sub