Split Excel worksheet into multiple workbooks

[Zlerp]

Hello,

I am looking to create a macro that will split up my Excel worksheet (over 200k rows). I want it to split every 10,000 rows and create a workbook and save it in the same file location as the file that the macro is ran on. I have data ranged from A1:L2000000. But the macro should continue through to the last row of data.

I currently have this code, shown below, that is supposed to split up the the workbook into multiple sheets but it only creates one new worksheet. I would prefer to create new workbooks and have them saved instead.



Sub Lime()Dim srcSheet As String
Dim RngLen As Long
RngLen = 10000
srcSheet = "Sheet1"
For x = 1 To 100 Step RngLen
Sheets(srcSheet).Range("A" & x & ":A" & x + RngLen - 1).Resize(, 12).Copy
Worksheets.Add After:=Worksheets(srcSheet)
ActiveSheet.Range("A1").PasteSpecial
Next x
End Sub




Let me know if you have any questions on what I'm looking for. Any help is appreciated.

Thanks,
Zlerp

[snb]

Sub M_snb()
for j=1 to (sheet1.usedrange.rows.count\10^5+1)*10^5 step 10^5
sheets.add ,sheets(sheets.count)
sheet1.cells(j,1).resize(10^5,12).copy sheets(sheets.count).cells(1)
Next
End Sub

[SamT]

Sweet! Hope he punches your Star