Zlerp
01-14-2015, 01:31 PM
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
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