- A+
所属分类:Excel
代码
最近遇到个小问题,excel表记录非常多,一百多万行,需要将excel表拆成多个表,于是想办法弄了个程序,一键分割表格
这个方法需要开启excel的“开发工具”,然后使用宏功能,编写vba代码,代码如下:
Sub IMonday() Dim r, c, i, WJhangshu, WJshu, bt As Long r = Range("A" & Rows.Count).End(xlUp).Row b = InputBox("请输入分表行数") If IsNumeric(b) Then WJhangshu = Int(b) Else MsgBox "输入错误", vbOKOnly, "错误" End End If c = Cells(1, Columns.Count).End(xlToLeft).Column bt = 1 '标题行数 'WJhangshu = 50 '每个文件的行数 WJshu = IIf(r - bt Mod WJhangshu, Int((r - bt) / WJhangshu), Int((r - bt) / WJhangshu) + 1) '------ Set fs = CreateObject("Scripting.FileSystemObject") ' For i = 0 To WJshu Workbooks.Add Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(i + 1, String(Len(WJshu), 0)) & "." & fs.GetExtensionname(ThisWorkbook.FullName) '扩展名 Application.DisplayAlerts = True ThisWorkbook.ActiveSheet.Range("A1").Resize(bt, c).Copy ActiveSheet.Range("A1") ThisWorkbook.ActiveSheet.Range("A" & bt + i * WJhangshu + 1).Resize(WJhangshu, c).Copy _ ActiveSheet.Range("A" & bt + 1) ActiveWorkbook.Close True Next End Sub
bt = 1表示第1至bt行是标题行,这样,分割的每张表都会带标题行,如果你的标题行占两行,将其改为2即可,表示前两行是标题行。
效果
代码下载
链接:https://pan.baidu.com/s/1RwuAv4Gb_wA4CXWyavU5Zg
提取码:pwc4