Sub 拆分工作表()
Dim b As Worksheet
Excel.Application.ScreenUpdating = False
For Each b In Sheets
b.Copy
Excel.ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & b.Name & ".xlsx"
Excel.ActiveWorkbook.Close
Next
Excel.Application.ScreenUpdating = True
End Sub
Sub 合并工作簿()
Dim Wb As Workbook, MyPath As String, File, Sh_n As String
Application.ScreenUpdating = False
Rem 关闭屏幕刷新
MyPath$ = ThisWorkbook.Path & "\"
Rem 获取当前工作簿路径
File = Dir(MyPath & "*.xls*")
Rem 获取路径下所有Excel文件
Do While File <> "" '遍历所有文件
If File <> ThisWorkbook.Name Then '不合并当前工作簿
Set Wb = Workbooks.Open(MyPath & File)
Rem 依次打开工作簿
Sh_n = StrReverse(Mid(StrReverse(Wb.Name), InStr(StrReverse(Wb.Name), ".") + 1))
Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = Sh_n
Rem 将第一个表复制到当前工作簿的最后一个工作表
Wb.Close False '关闭工作簿 不保存
End If
File = Dir
Rem 循环下一个工作簿
Loop
Application.ScreenUpdating = False
Rem 打开屏幕刷
End Sub
模板在手,以后不管要拆分、合并,都是轻轻松松一键搞定,再也不用为这些事发愁。
推荐:别再复制粘贴了,几十个工作表合并最简单的办法,一学就会!
你怕不怕长期跟卢子学习,以后一天的工作几分钟做完?
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服