+ Reply to Thread
Results 1 to 11 of 11

Extracting data from one massive worksheet to split to smaller sheets of data

  1. 05-20-2008, 03:24 AM #1
    michaelkwc
    michaelkwc is offline
    Registered User
    Join Date
    05-14-2008
    Posts
    56

    Extracting data from one massive worksheet to split to smaller sheets of data

    Hello again. Been racking my head over this but simply cant get any solution. I'd really appreciate it if someone can help me out with this.

    10/05/08 08:30:00 AM Valid Card Entry Lam Door 1
    10/05/08 08:31:00 AM Door Leave Open Door 1
    10/05/08 08:32:00 AM Door Closed Door 1
    10/05/08 08:41:00 AM Valid Card Exit Yap Door 1
    10/05/08 08:46:00 AM Valid Card Exit Lam Door 2
    10/05/08 09:14:00 AM Valid Card Entry Lam Door 2
    10/05/08 10:18:00 AM Valid Card Exit Lam Door 2
    10/05/08 10:19:00 AM Valid Card Entry Yap Door 1
    10/05/08 10:40:00 AM Valid Card Exit Yap Door 1
    10/05/08 10:42:00 AM Valid Card Entry Lam Door 2
    11/05/08 08:30:00 AM Valid Card Entry Lam Door 2
    11/05/08 08:20:00 AM Valid Card Entry Yap Door 1
    11/05/08 10:20:00 AM Valid Card Exit Lam Door 1
    11/05/08 11:40:00 AM Valid Card Exit Yap Door 1

    the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet,
    e.g. at sheet 2 (renamed to "lam") should be

    10/05/08 08:30:00 AM 11/05/08 08:30:00 AM
    10/05/08 08:46:00 AM 11/05/08 10:20:00 AM
    10/05/08 09:14:00 AM
    10/05/08 10:18:00 AM
    10/05/08 10:42:00 AM


    and sheet 3 (renamed to "yap") should be something like,

    10/05/08 08:41:00 AM 11/05/08 08:20:00 AM
    10/05/08 10:19:00 AM 11/05/08 11:40:00 AM
    10/05/08 10:40:00 AM


    this is only a sample data, the actual datasheet would contain over 100 names with over 20 entries per person, for a duration of 1 whole month. which is why i need to split everything up. can someone help me please? i'm learning VB as much as i can but i need as much help as i can get - everybody is a newbie at least once eh?

    appreciate the help, thanks in advance!
    Reply With QuoteRegister To Reply
  2. 05-20-2008, 06:11 AM #2
    Charlize
    Charlize is offline
    Forum Contributor
    Join Date
    03-25-2008
    MS-Off Ver
    Excel, Outlook, Word 2007/2003
    Posts
    245
    Maybe you could give this a try. 
    Please Login or Register  to view this content.
    Charlize
    Reply With QuoteRegister To Reply
  3. 05-20-2008, 10:55 PM #3
    michaelkwc
    michaelkwc is offline
    Registered User
    Join Date
    05-14-2008
    Posts
    56
    i'm getting the error message "Subscript out of range", and at the debugger it highlights the line
    Set ws = wb.Worksheets("Data")
    (at the top area of the code). can someone please teach me how to correct this? thanks!

    also if say, i have more than just "lam" and "yap", say perhaps 100 people, can i just replace the line
    For Each myitem In Array("Lam", "Yap")
    with
    For Each myitem In Array(Columns("D:D"))
    instead? i would love to test it myself instead of asking but i can't run it due to the 1st problem above...
    Last edited by michaelkwc; 05-21-2008 at 12:34 AM.
    Reply With QuoteRegister To Reply
  4. 05-21-2008, 02:43 AM #4
    Charlize
    Charlize is offline
    Forum Contributor
    Join Date
    03-25-2008
    MS-Off Ver
    Excel, Outlook, Word 2007/2003
    Posts
    245
    Please Login or Register  to view this content.
    means that the info to be processed is on a worksheet called Data. So the tab should be Data or change the coding to use the name of your worksheet where the starting data is located.

    for your second problem you could create a list of all the names you want. Several possibilities. A worksheet, a txt file, a worksheet on another workbook, the windows registry ...

    Charlize
    Reply With QuoteRegister To Reply
  5. 05-21-2008, 04:01 AM #5
    michaelkwc
    michaelkwc is offline
    Registered User
    Join Date
    05-14-2008
    Posts
    56
    could've smacked myself in the head for not figuring out the 1st problem haha... anyway thanks, but now when i try to run it, nothing happens. only the msgbox pops up telling me all data has been processed but nothing actually happens...

    edit: 

    Please Login or Register  to view this content.
    modified line 2 to be >= 0, and line 3 to Array(Columns("D:D")) to try to get it to work, but now i get the error "Type mismatch" and the debugger highlights line 4. could this have anything to do with "myitem" not being initialized or something like that? because prior to that sentence, "myitem" was only declared as a variable...

    appreciate all the help, thanks a lot, i'll die at work if not for you lol...
    Last edited by michaelkwc; 05-21-2008 at 04:32 AM.
    Reply With QuoteRegister To Reply
  6. 05-21-2008, 04:23 AM #6
    Charlize
    Charlize is offline
    Forum Contributor
    Join Date
    03-25-2008
    MS-Off Ver
    Excel, Outlook, Word 2007/2003
    Posts
    245
    Maybe you could post a sample workbook with some data in it and your codings already included.

    Charlize

    ps.: which version of excel ? (2003, 2007 ?)
    Reply With QuoteRegister To Reply
  7. 05-21-2008, 04:33 AM #7
    michaelkwc
    michaelkwc is offline
    Registered User
    Join Date
    05-14-2008
    Posts
    56
    used openoffice to create the original *.xls (Microsoft Excel 97/2000/XP), now using a trial version of Excel 2007 to modify the coding.

    a copy as per your request (it's on sheet 3):
    Attached Files Attached Files
    Last edited by michaelkwc; 05-21-2008 at 04:53 AM.
    Reply With QuoteRegister To Reply
  8. 05-21-2008, 05:22 AM #8
    Charlize
    Charlize is offline
    Forum Contributor
    Join Date
    03-25-2008
    MS-Off Ver
    Excel, Outlook, Word 2007/2003
    Posts
    245
    Variation based on sheet 3. 
    Please Login or Register  to view this content.
    Charlize
    Reply With QuoteRegister To Reply
  9. 05-21-2008, 05:30 AM #9
    michaelkwc
    michaelkwc is offline
    Registered User
    Join Date
    05-14-2008
    Posts
    56
    awesome. worked perfectly. thanks particularly for the comments in-code, it really helps in my learning of VB. thanks a lot charlize for the help, you've inspired me to learn more about VB and one day return here to contribute back to the society.

    again, thank you.
    Reply With QuoteRegister To Reply
  10. 05-21-2008, 05:34 AM #10
    Charlize
    Charlize is offline
    Forum Contributor
    Join Date
    03-25-2008
    MS-Off Ver
    Excel, Outlook, Word 2007/2003
    Posts
    245
    Your welcome. The issues we had were based on the fact that I thought that everything was located in one cell instead of seperated columns. Anyway, glad that, in the end, everything worked as expected.

    Charlize

    ps. Perhaps it's better to rename the function that checks if a worksheet exists. Now it's named workbook_exists ... could lead to misunderstandings. Change the coding in your macro if you change the name of this function.
    Last edited by Charlize; 05-21-2008 at 05:38 AM.
    Reply With QuoteRegister To Reply
  11. 05-21-2008, 06:35 AM #11
    michaelkwc
    michaelkwc is offline
    Registered User
    Join Date
    05-14-2008
    Posts
    56
    i'll check it but dont think it'll make much diff as i dont think the office staff i'm doing this for will understand anything lol. they use excel everyday yet have no idea what is a macro... lol. anyway thanks again for everything, truly appreciate it.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1