Splitting a Large Spreadsheet into Individual Files by Sum While Retaining Headers

[starrchilde]

Happy Friday!

I am attempting to split a large excel spreadsheet into multiple files based on the sum value of variable rows, is it possible to do this? I have used <a href="Looking to split a large excel file by rows and keep the header"this</a> to split by rows, which works fantastically, but I'm not sure how to convert it to look for sum values instead of number of rows, or if it's even possible.

Ie: I have a spreadsheet with 25k+ rows and a combined value of 5.x million - I need to break these down into individual spreadsheets of 500k while retaining existing headers.

Thank you!

 

[Akuini]

but I'm not sure how to convert it to look for sum values instead of number of rows,

Can you post an example? maybe just 10-20 rows with only the relevant column to do the sum. And explain what should happen.

 

[starrchilde]

Hi Akuini, this is what my current spreadsheet looks like. I'm would like to run a script which will look at the values in column D and sum to X, creating a new spreadsheet for each grouping. So if the value indicated is $500, then I would like it to run through, creating unique spreadsheets with all data from columns A-D, stopping at the closest value $500.

I hope this makes sense, please definitely let me know if I need to elaborate more!

Thank you,

 

[Akuini]

I'm would like to run a script which will look at the values in column D and sum to X, creating a new spreadsheet for each grouping. So if the value indicated is $500, then I would like it to run through, creating unique spreadsheets with all data from columns A-D, stopping at the closest value $500.

So, the grouping criteria is the sum of col D doesn't exceed 500?
But i don't understand this part:

I have a spreadsheet with 25k+ rows and a combined value of 5.x million - I need to break these down into individual spreadsheets of 500k while retaining existing headers.

what do you mean by "I need to break these down into individual spreadsheets of 500k"?
So, is it 500 or 500K?

This code use 500 as the criteria, you can change it in this part: If x > 500 Then

Sub starrchilde_1()
Dim wsA As Worksheet
Dim i As Long, k As Long, j As Long, x As Double
Dim va, Hdr
Application.ScreenUpdating = False


Set wsA = ActiveSheet
Hdr = Range("A1:D1")
    va = Range("D1", Cells(Rows.Count, "D").End(xlUp))
    va(1, 1) = 0
    j = 2

For i = 2 To UBound(va, 1)
    x = x + va(i, 1)
    If x > 500 Then
        k = k + 1
        With Worksheets.Add(After:=Sheets(Sheets.Count))
            .Name = "try_" & Format(k, "0000")  'new sheet name
            .Range("A1:D1") = Hdr
            .Range("A2").Resize(i - j, 4).Value = wsA.Range("A" & j & ":D" & i - 1).Value
            j = i:   x = va(i, 1)
        End With
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[starrchilde]

Thank you so much, and I apologize for the confusion around the sum value. In the original spreadsheet that prompted the request it was $500k, in the subsequent one which I needed to do this with, the value was $500.

 

[Akuini]

Ok, did the code work as expected?

 

[starrchilde]

Please forgive my delayed response, I was traveling for work.

When I enter it and attempt to run it, I get a Runtime Error 13: Type mismatch

 

[Akuini]

Please forgive my delayed response, I was traveling for work.

When I enter it and attempt to run it, I get a Runtime Error 13: Type mismatch

The sheet in question must be the active sheet when you run the code.