Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Breaking It Down (Excel 97)

    OK, I need to build a VBA utility macro and thought maybe I would check to see if someone hasn't already done this before I start re-inventing the proverbial wheel. Does anything like this exist already?

    Assume I have a "Master" file of CSV formatted data containing a "category" in the first field of each record (the rest of the data is anything you want). Below is a simplified example of this...

    ======================
    CATEGORY, NAME, AGE, OCCUPATION
    MAN, Mike, 40, architect
    WOMAN, Carol, 29, homemaker
    BOY, Greg, 17, student
    GIRL, Marsha, 17, student
    BOY, Peter, 12, student
    GIRL, Jan, 12, student
    BOY, Bobby, 7, student
    GIRL, Cindy, 7, student
    MAN, Sam, 50, butcher
    WOMAN, Alice, 29, housekeeper
    DOG, Tiger, 3, pet
    ======================

    Assume I have correctly imported this file to Sheet1 of a blank workbook. What I need is a macro that will loop through this table, create a new worksheet for each category and move the Sheet1 data from columns B..D of each row to columns A..C on the appropriate worksheet. The macro will need to rename each new worksheet as it is created with the category name ( MAN, BOY, DOG, etc... ).

    A variation on this would be to have a the data moved into an Access database table instead of a worksheet, appending new data to existing tables and creating new tables as needed for new category labels.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking It Down (Excel 97)

    If you only have to do this once, it would probably be faster to use the autofilter function and manually copy and paste each category to each sheet and then rename the sheet to the category type.

    By the time you have a macro working that will do this, you probably would have finished. (to simplify things you could probably record a macro that will copy, paste and rename the destination sheet to the category in cell A2[assuming the the headings are also copied across], you then just have to change the filter to the next category and repeat the macro)

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking It Down (Excel 97)

    The macro below should do what you want. I have attached a workbook that shows demonstrates it.

    <pre>Public Sub CreateSheets()
    Dim oSheet As Worksheet
    Dim I As Long, lLastRow As Long, lDestRow As Long
    lLastRow = Worksheets("Category").Range("A1").Offset(Workshee ts("Category").UsedRange.Rows.Count, 0).End(xlUp).Row
    For I = 0 To lLastRow - 2
    Set oSheet = Nothing
    On Error Resume Next
    Set oSheet = Worksheets(Worksheets("Category").Range("A2").Offs et(I, 0).Value)
    On Error GoTo 0
    If oSheet Is Nothing Then
    Set oSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oSheet.Name = Worksheets("Category").Range("A2").Offset(I, 0).Value
    oSheet.Range("A1").Value = Worksheets("Category").Range("B1").Value
    oSheet.Range("C1").Value = Worksheets("Category").Range("D1").Value
    End If
    lDestRow = oSheet.Range("A1").Offset(oSheet.UsedRange.Rows.Co unt, 0).End(xlUp).Row
    oSheet.Range("A1").Offset(lDestRow, 0).Value = Worksheets("Category").Range("B2").Offset(I, 0).Value
    oSheet.Range("C1").Offset(lDestRow, 0).Value = Worksheets("Category").Range("D2").Offset(I, 0).Value
    Next I
    End Sub
    </pre>

    Attached Files Attached Files
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Feb 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking It Down (Excel 97)

    This looks perfect. I will have to plug it in and try it at work Monday. Thank you. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  5. #5
    New Lounger
    Join Date
    Feb 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking It Down (Excel 97)-MULTI-Re: Breaking It Down

    Just wanted to drop a line and say this worked **perfectly** for my needs with minor tweaking (number of columns being moved was about 12). Thank you so much for the assist!

    Now for "fun" how about a macro that will do the exact reverse? I don't need it but it seems like an interesting idea to work on. I will if no one else wants to try - might be a good learning experience for me.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Breaking It Down (Excel 97)-MULTI-Re: Breaking It Down

    Hi Legare

    I am interested in your use of
    lLastRow = Worksheets("Category").Range("A1").Offset(Workshee ts("Category").UsedRange.Rows.Count, 0).End(xlUp).Row
    ..in your previous response.
    Is there an advantage compared to say, using..
    lLastRow = Worksheets("Category").Range("A1").CurrentRegion.R ows.Count

    zeddy

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking It Down (Excel 97)-MULTI-Re: Breaking It Down

    CurrentRegion does not always do what you want if there are empty cells/rows/columns.
    Legare Coleman

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Re: Breaking It Down (Excel 97)-MULTI-Re: Breaking It Down

    What if the empty cells are at the bottom of ColumnA in your example - won't the End(xlUp) skip up to the first non-empty cell - leaving rows at the bottom??

    zeddy

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking It Down (Excel 97)-MULTI-Re: Breaking It Down

    Yes, you need to pick a column that will have something in the last line.
    Legare Coleman

Posting Permissions

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