Excel按照固定行数拆分成多个文件

发布网友发布时间：2022-11-29 21:05

共1个回答

热心网友时间：2023-10-28 22:23

Sub cfb()

Dim r, c, i, WJhangshu, WJshu, bt As Long

r = Range("A" & Rows.Count).End(xlUp).Row

c = Cells(1, Columns.Count).End(xlToLeft).Column

bt = 1 'title

WJhangshu = 200 'num

WJshu = IIf(r - bt Mod 20000, Int((r - bt) / WJhangshu), Int((r - bt) / WJhangshu) + 1)

For i = 0 To WJshu

Workbooks.Add

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(i, String(Len(WJshu), 0)) & ".xlsx"

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

注：如果数据量很大，又拆分的很小，最好选择“单步执行”，否则会很卡....（电脑性能好当我没说）