Split Data into Multiple Sheets based on Column (45K rows of Data, Overflow error HELP)

[apurcell]

Hi everyone. First time posting. The problem is exactly as described. I am new to MACROS and VBA.
This is the code I am using (Thank you to the individual who posted it) Split data into multiple worksheet based on column variables - edited from online sources
on the line i = 2 to lr, it is giving me a yellow arrow. and says Overflow error

I imagine because I have 45K rows of data.

I like this MACRO because it lets you pick which column you want to split on. I have different data sets of different lengths and we need to split on different columns for each on.

I appreciate any help in advance.

Sub parse_data()
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer

'This macro splits data into multiple worksheets based on the variables on a column found in Excel.
'An InputBox asks you which columns you'd like to filter by, and it just creates these worksheets.

Application.ScreenUpdating = False
vcol = Application.InputBox(prompt:="Which column would you like to filter by?", title:="Filter column", Default:="3", Type:=1)
Set ws = ActiveSheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = "A1"
titlerow = ws.Range(title).Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")
'Sheets(myarr(i) & "").Columns.AutoFit
Next
ws.AutoFilterMode = False
ws.Activate
Application.ScreenUpdating = True
End Sub

 

[dmt32]

Hi @dmt32 and @gtwo
Im sorry I probably didnt make it clear in my request. We work with two seperate data sets. On one of them, I need to Sum Column 8 after each change in column 1 and the other data set: I need to sum Column 9 on each change in column 5. I was hoping for a way where it would ask me to type in which row I would like to sum. but if not that would still be very useful and I could just change the code for each one.

Have you tried code I posted? If not quite doing what you want post screen shot of a filtered page showing desired outcome

Dave

 

[apurcell]

Hi @dmt32, I tried your code and it isn't what I am looking for. The issue is that I don't want it to put each subtotal on each sheet. Here is some screenshots of what I am doing and what I am looking for. So the current MACRO I referenced above starts off exactly how I want it to. It asks which column I want to split to different sheets by. This helps us split all the entries by department.

Each department then gets it own tab with their own entries for all their respective accounts. (This is exactly what I wanted this far!)

Here is what I currently have to do manually: I have to go into each Department Tab and subtotal at each change in the Account Description Column using the fx SUM and add that subtotal to the Amount Column. This is what I want to MACRO to do automatically after it splits each department.

Here is what it looks like once you have finalized it. I then have to go into all 58 departments and do this for both data sets that we have. I usually just create a MACRO that does this but it often breaks and still takes a long time. Would be great if it just did the subtotal when it split them into departments. There might be many entries/transactions to subtotal, or sometimes (like pictured below) there is just one per account. I need the subtotal for each Account for each department sheet.

Thank you for all your help so far!! Let me know if I can clarify further. I'd love to just find something to add to the above mentioned MACRO as it already works great.

 

[dmt32]

Hi,
if you can place copy of your master sheet with sample data here using MrExcel XL2BB AddIn (XL2BB - Excel Range to BBCode )
I will in absence of another solution being posted, see if can provide an update to my code.

Dave