VBA: Split data into multiple worksheets based on column

[waxsublime]

I'm trying to get this code I found (from How to split data into multiple worksheets based on column in Excel?) to work, but it's giving me an error.

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
    vcol = 4
    Set ws = Sheets("Sheet1")
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = "A1:I1"
    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
End Sub

Any ideas on how to fix this?

Thanks!

 

[DocAElstein]

Got that error cleared out. Thanks for pointing me in the right direction.

Now I am getting "Invalid procedure call or argument" but MVB is not highlighting where the error is ( which it normally does when you run debug ). Anyway to force which line is causing the error?

PS, I am lost on the bear thing. I know you have a bear in your avatar but I do not know if that is the reason for it.

... I just take out my frustrations sometime on the Bear.......he is resting now... just had a half hour go on him... it was a hard day..


.........

. Anyways... if you can step through the code in the With F8 in the development Window (What you get when you hit Alt + F11 ) it should hopefully go through until it hits the popint giving the error.

. Difficult from this distance to guess what might be going wrong..

"Invalid procedure call" sounds maybe not liking the UDF again, but I am just guessing.


....
Alan

 

[ArmyGuy45]

Ok so, I have data in Column E" and it seems to move that data over one column after every error. So could the error be because there is information in column E already? I ran the macro 5 times with the now and now my data from column E is now in column J and I have blank columns from E to I. So if we switch the temporary column to column F instead, would that fix it?

 

[DocAElstein]

Ok so, I have data in Column E" and it seems to move that data over one column after every error. So could the error be because there is information in column E already? I ran the macro 5 times with the now and now my data from column E is now in column J and I have blank columns from E to I. So if we switch the temporary column to column F instead, would that fix it?

Hi..

........ I guess if it errors after creating that temporary column then the program never gets to the point where it delete that column...
.. so logically every time you re-run it keeps making an extra one..
..
. I do note now that as I have written the code it copies an unwanted temporary column E to each sheet. I overlooked that. But that should be easy to rectify. (If you think about it, if you have some idea of how the program is working then you simply need to add an extra line

.Columns("E:E").Delete

to delete that unwanted column E after each new sheet is finished. ......
. I just did that mod here on your sample File and that sorts that one out..
. I won't post the code again just for that simple mod as
.1 ) The Thread is already a bit cluttered with multiple copies of your code..




...But more importantly
. 2) that is not the main issue. The main issue is why it is erroring.

Alan
.

. keep trying and keeping me informed, but as I mentioned, it is difficult to see at this distance what the problem is. If anything springs to mind I will let you know

. If you drop me off a file with all macros you are trying I will check it out when I have time .. but as I am a bit busy just now keep trying yourself.
. Please note my comment from post #62 – I will not do my head in if you send a massive file with lots of long strings in it….(The Bear’s had enough for a day or so…)

..

Alan

 

[ArmyGuy45]

I have seemed to isolate the error to line number 29 which is the UDF. When I perform the 'run to cursor' before line 29 I have no issues but if I place the cursor anywhere after line 29 I get the "Invalid procedure call or argument". If I comment the line out the script runs right through with no errors but also does not split out the information into other fields. Below is the line 29:

Let rngD.Offset(0, 1).Value = UDArmyGeezerPickOutBit(rngD, "OU=Groups,OU=", ",") 'Our UDF is set to work with our Range in Column D. The Offset Property bit is just a neat - here it returns a range that is one place to the right. That saves us having to Dimension and set a rngE to use in an alternative code line RngE = UDArmyGeezerPickOutBit(rngD, "OU=Groups,OU=", ",")

What would be the next trouble shooting step?

 

[DocAElstein]

......

What would be the next trouble shooting step?

. I lack the experience to help you any more, working "blind" at this distance

. see my notes Post #83, especially the bit about dropping me off a file

 

[DocAElstein]

P.s.

..what were your results from testing the UDF in the spreadsheet as I explained in Post #61 ?

 

[HappyLadyToo]

I know this is an old post, but if you see this Mirabeau, thanks a million!!

Really depends on the data and the problem.
You could try this one and see if it works for you

 

[stbuckley89]

Hi I am also looking to split the contents from the main sheet into other worksheets.
I split the the data based on the hour a request was closed.
The business operates from 07:00 - 19:00 so requests will be closed during each of these hours.
The defining column is "Modified Date" an example of the data is 4/21/15 8:17.

Can anybody help me with this? My knowledge is fairly limited.

 

[DocAElstein]

Hi I am also looking to split the contents from the main sheet into other worksheets.
I split the the data based on the hour a request was closed.
The business operates from 07:00 - 19:00 so requests will be closed during each of these hours.
The defining column is "Modified Date" an example of the data is 4/21/15 8:17.

Can anybody help me with this? My knowledge is fairly limited.

. Hi stbuckley89, Welcome to the board.
. I am still a bit of a VBA novice myself, but at first glance a simple modification to your spreadsheet should allow you to be able to use some of the codes in this thread.
. That mod would be to make an extra column with the hour in it. Then use that column as the defining column.
. A formula or an extra bit of VBA code could possibly simplify getting just the hour from your full time and date format. A bit of googling should help here.
. I am away from my excel computer for a while.
. In any case if you require more detailed help, a clearer copyable screenshots shots showing what you want is always helpful. – see the various notes from me in this thread Post #21 on how to do that..
. If you do that and maybe make some attempt to modify some of my codes and still have difficulties, and no-one else picks up the thread then get back and I will have a good look at your data and try to get a working code for you in a day or two.
. Alan

 

[Ken_W]

Hi Karan..
. I tried to understand your code. I could not fully understand it, but was just about able to see the similarities with mine in order to apply my “Bodge” to your code
.

. Alan.

P.s. The File again with all macros in module “Alan” (This time only with the sheet 1 there so you can test the “Bodged” versions of your code).
https://app.box.com/s/cm8ma7jb55z9tx7hb2to

Hi Alan,
To start, I consider myself an expert user of Excel, but a complete novice when it comes to VBA. Nevertheless, after several attempts of using code provided throughout this thread, I found that this version would work for me (your #38 post). However, it only works based on column A. I saw other versions of code where this could be modified but was not able to get it to work for me due to my limited understanding of what I am doing. I need it to work as is but based on column F in my file. Ideally, I’d like to be able to specify the column within the code as I’ll use this in various spreadsheets.

I’d also like to have an ActiveX control button associated with the code so that multiple users can use the spreadsheet as a template where they can paste in the data and then select the button to parse the data. Due to security reasons, our Excel Macros are disabled, but I think the ActiveX will work.

I’ve saved a copy of my file here:
https://app.box.com/s/t2sis1m5m0cmqjqhnulr0xfxizagjtz4

As you’ll see, I am working with a lot of data and this is only a small portion. I suspect I’ll be limited in the amount of rows I can do at one time. I’ve seen some of your post where you mention too much data being a problem. I greatly appreciate any assistance you can provide.

Ken