Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Creating tabs based on Column A (Excel 97)

    Hi there,

    A while back someone here helped me out by providing some code. I have extracted a piece of the code that has my worksheet automatically create new tabs based on Column A and to copy the information over to the correct tab. But it is not working exactly~ I think there is stuff in there from the original code":

    Dim I As Long, lFirst As Long, lLastRow As Long
    Dim strName As String
    Dim oWS As Worksheet
    lLastRow = Worksheets("Sheet1").UsedRange.Rows.Count
    Worksheets("Sheet1").Range("A1:AO" & lLastRow).sort Key1:=Worksheets("Sheet1").Columns("A"), _
    Order1:=xlAscending, Header:=xlYes
    lFirst = 1
    Do While Worksheets("Sheet1").Range("A1").Offset(lFirst, 0).Value <> ""
    strName = Worksheets("Sheet1").Range("A1").Offset(lFirst, 0).Value
    For I = lFirst To lLastRow
    If strName <> Worksheets("Sheet1").Range("A1").Offset(I + 1, 0).Value Then Exit For
    Next I
    Set oWS = Nothing
    On Error Resume Next
    Set oWS = Worksheets(strName)
    On Error GoTo 0
    If oWS Is Nothing Then
    Set oWS = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWS.Name = strName
    End If
    oWS.Cells.ClearContents
    Worksheets("Sheet1").Range("1:1").copy Destination:=oWS.Range("A1")
    Worksheets("Sheet1").Range("A" & lFirst + 1 & " :A" & I + 1).EntireRow.copy Destination:=oWS.Range("A2")
    lFirst = I + 1
    Loop

    The worksheet has 4515 rows. It creates all the tabs, but when it gets to the larger groups of data, it only copies over the first 2 rows. I'm not really good at this VBA stuff, so I was hoping someone could help me with this..

    Thank you!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating tabs based on Column A (Excel 97)

    I can't reproduce this - I tested on a worksheet with over 8,000 filled rows. Could you create a copy of the workbook, with only Sheet1 in it and most data removed (except for column A of course)? Save the copy and put it in a zip file; attach the zip file to a reply (it should be below 100 KB)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Creating tabs based on Column A (Excel 97)

    Thank you so much for telling me it worked for you. That helped me solve my issue. The problem was that some of the items in column A were in Uppercase & some in lowercase. (I didn't realize the code would evaluate it literaly) Once I changed all the data to uppercase I reran the code and had no problem.

    Thank you so much Hans, for looking at this for me!

Posting Permissions

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