Page 9 of 12 FirstFirst ... 7891011 ... LastLast
Results 81 to 90 of 111

Thread: VBA: Split data into multiple worksheets based on column

  1. #81
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by ArmyGuy45 View Post
    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

  2. #82
    New Member
    Join Date
    Jan 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    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?

  3. #83
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by ArmyGuy45 View Post
    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

    Code:
    .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

  4. #84
    New Member
    Join Date
    Jan 2015
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    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?

  5. #85
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by ArmyGuy45 View Post
    ......

    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

  6. #86
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    P.s.

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

  7. #87
    Board Regular
    Join Date
    Aug 2012
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

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



    Quote Originally Posted by mirabeau View Post
    Really depends on the data and the problem.
    You could try this one and see if it works for you
    Last edited by RoryA; Jan 20th, 2017 at 07:47 AM.

  8. #88
    New Member
    Join Date
    Apr 2015
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    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.

  9. #89
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by stbuckley89 View Post
    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

  10. #90
    New Member
    Join Date
    May 2015
    Location
    Pensacola, FL
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Split data into multiple worksheets based on column

    Quote Originally Posted by DocAElstein View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •