1. 如下Excel表,总共有120多行数据,如何将以50行数据为一个工作表进行拆分
Sub ZheFenSheet()
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 = 2 '标题行数
WJhangshu = b '每个文件的行数
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
Excel 拆分
最新推荐文章于 2024-05-16 21:22:33 发布