Split worksheet into worksheets based on conditions

[gvf]

Hello all.
This is my first post in this forum and english isn't my main language, so forgive me if i'm not clear enough or something. Also, I'm using excel 2007 on windows XP SP3

So, i searched a lot and couldn't find any precise answers about issues that arise when we are working on very large datasets. My dataset contains 610.000+ observations, and its a pain to do anything - it crashes a lot, it's very slow and such. Is that normal? Can I do anything to improve it or make it less painful?

With that in mind I was thinking about splitting my dataset so , hopefully, it becomes easier to deal with the amount of data. But i don't want to do it manualy, so I tried to creat a macro or vba procedure, but with no sucess... So here I am.

Ok, I have a dataset with 610.000+ cases and 50+ columms. I wanted to split my main worksheet into various smaller worksheets, based on at least one condition - a columm called "state" - , so I'd have a worksheet for each state present on my main worksheet. How can I do that? And with more than one condition, what do i have to add?

I'm not very good with programming, but I'm at a point that I need to learn it, to stay competitive in my career. Does anyone know a good guide/book/internet page that offers something close to a complete course to excel and vba? I've already done almost all of the "VBA for dummies" book and I still have so much to learn...

Thank you in advance.
Gvf.

 

[VoG]

Hi. See if this helps http://www.mrexcel.com/forum/showthread.php?t=396069

 

[gvf]

Hi. See if this helps http://www.mrexcel.com/forum/showthread.php?t=396069

Thank you, it did help!

But I had to recode the variable that i used as filter to numeric. Is there a way to make it work with string type variables?

 

[VoG]

Sorry, I'm not sure what you mean. Did you use Excel Explosion or my code?

 

[gvf]

I used your code, even because i can't install anything in this computer.

Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("H2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To lastrow
If .Range("H" & i).Value <> .Range("H" & i + 1).Value Then
iEnd = i
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = .Range("H" & iStart).Value
On Error GoTo 0
ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
.Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
iStart = iEnd + 1
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

But i mean, in your code, as far as i understood, it creates new worksheets based on the "H" columm. But if the H columm isn't numeric, like in my data? I let it ran for about 20 minutes and the result was a lot of worksheets, each with varying number of cases. Then i recoded my H columm to numeric, and it worked wonders. So i wanted to know if there's a way to make the code work with non-numeric condition data (tweaking it, i suppose). Hope i made myself clear this time...

 

[VoG]

It works for me with non-numeric data - that's what I originally developed it for (splitting out data by city).