Split Excel Worksheet in Different Files and Copy Data from Specific Columns to the new files

Question

Alright so I have an excel file that's filled up with numbers from the first to the last row in column A.What i need to do is split the file into different files for every 50th thousand row (so basically 20 new files) and copy paste the numbers so they correspond to the order of the splitting process.So basically the first new file should have the numbers from row 1 to row 50000 then the second file should have the numbers from row 50000 to row 100000 and etc.I've managed to split it into 20 different files but have no idea at how to actually copy-paste the data. This is what i have so far:

Sub splitBook()

Dim cell As Range
Dim xPath As String
Dim i As Long, row As Long, lastRow As Long
Dim counter As Integer
Dim wb As Workbook
Dim rows As Range
counter = 0
Dim broi As Integer
broi = 20

xPath = "C:\Users\User\Documents\Test"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

For Each cell In Sheets("Test").Range("ColumnA")
    If i = 0 Then

                Workbooks.Add
                Set wb = ActiveWorkbook
                ThisWorkbook.Activate
    End If

            i = i + 1
            If i = 50000 Then

                counter2 = counter2 + 1
                wb.SaveAs Filename:=xPath & " file number " & CStr(counter2)
                wb.Close
                Set rows = Nothing
                i = 0

            End If
        Next cell

Set wb = Nothing
Application.DisplayAlerts = True

End Sub

Could you just tailor this example? https://yodalearning.com/tutorials/how-to-split-spreadsheet-into-separate-workbooks-using-excel-vba/ That's actually a little more sophisticated than you need because it applies filtering, whereas you just need a fixed number of rows. — Marc, Oct 29 '18 at 16:15
Alternatively, you could just export to CSV and use a linux/mac/cygwin `split` command, as described here: https://stackoverflow.com/questions/25249516/split-text-file-into-smaller-multiple-text-file-using-command-line — Marc, Oct 29 '18 at 16:20
When you get to `i = 50000`, you want to create a range, copy it, and paste it. You'll have to reference the workbooks. It'd be something like `Workbooks("mainWB").Worksheets("Data").Range("A1:A" & i").Copy // wb.Sheets(1).Range("A1:A" & i).Paste` ...then tweak that `Range("A1:A..."` part) — BruceWayne, Oct 29 '18 at 16:20

score 1 · Accepted Answer · answered Oct 29 '18 at 16:29

1

Alternatively, you could create a master range, and step through every 50,000 rows:

Sub new_workbooks()
Dim xpath As String: xPath = "C:\Users\User\Documents\Test"
Dim groupCount As Long: groupCount = 50000
Dim counter2 As Long: counter2 = 1
Dim rng As Range, tmpRng As Range

Dim totalRows As Long, totalNewBooks As Long
totalRows = Cells(Rows.Count, 1).End(xlUp).Row

Dim i As Long
Dim newWB As Workbook
For i = 1 To totalRows Step groupCount
    Set tmpRng = Range("A" & i & ":A" & i + groupCount - 1)
    Set newWB = Workbooks.Add
    tmpRng.Copy newWB.Sheets(1).Range("A1")
    Application.CutCopyMode = False
    newWB.SaveAs Filename:=xpath & " file number " & CStr(counter2)
    newWB.Close
    counter2 = counter2 + 1
Next i

End Sub

answered Oct 29 '18 at 16:29

BruceWayne

22,923
15
65
110

Yeah that helps controlling the range from 1 to 50 k but now i have trouble looping it 20 times so it can go on.Thanks a lot though I'll try playing around :) – Kaloyan Oct 29 '18 at 17:11
@Kaloyan - Ah, I forgot to add the `Application.ScreenUpdating = False // Application.DisplayAlerts = False // Application.EnableEvents = False` to this, which may help. Also, try adding `Application.Calculation = xlManual`. When you say you have "trouble", what do you mean? Errors? Or does it just slow down after a while? – BruceWayne Oct 29 '18 at 17:13
What i need the code to do is split the first big file to parts, where it splits on every 50th thousand number, and since i filled it to the end it basically needs to make 20 files with the last one starting from the 968*** row and ending on the 108**** row.Your code works perfectly but it makes only 1 file from the first row to row number 50000.I tried combining it with my code since I managed to split it into 20 files but it didn''t work out – Kaloyan Oct 29 '18 at 17:51
I managed to figure it out, thank you though the code helped a lot :) – Kaloyan Oct 30 '18 at 15:39

Split Excel Worksheet in Different Files and Copy Data from Specific Columns to the new files

1 Answers1