(Solved) – VBA Left Function?

(solved)-–-vba-left-function?

I’m relatively new to VBA and have some code I wrote that seems like it should be straightforward but is not behaving as expected. I am trying to separate my primary WorkSheet (GAWi) into three other worksheets (LWi, WMi, & OTi) based on the first letter in column H. Basically if the first letter is “L” I want that row to be copied and pasted onto sheet LWi and then deleted from the original sheet. Then if it is W it goes onto WMi and if it is A it goes onto OTi. It is functioning properly for the first two If statements (placing items that begin with L & W onto the correct sheets), but for the last one items that begin with P and 0 are also being placed onto sheet OTi. I’m at a complete loss, it seems pretty easy and I can’t figure out where I went wrong. Any advice would be much appreciated, also I’m sure this code is pretty unelegant by most standards so any tips on how to shorten it would also be welcomed-I’ve just started getting into VBA in the last couple weeks. Thank so much!

  Sheets("GAWi").Select
    Columns("H:H").Select
    Dim lwr As Range
    Set lwr = ActiveSheet.UsedRange
        For i = lwr.Cells.Count To 1 Step -1
        If Left(lwr.Item(i).Value, 1) = "L" Then
            lwr.Item(i).EntireRow.copy
            Sheets("LWi").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveCell.Offset(1, 0).Select
            Sheets("GAWi").Select
            lwr.Item(i).EntireRow.Delete
    End If
        If Left(lwr.Item(i).Value, 1) = "W" Then
            lwr.Item(i).EntireRow.copy
            Sheets("WMi").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveCell.Offset(1, 0).Select
            Sheets("GAWi").Select
            lwr.Item(i).EntireRow.Delete
    End If
        If Left(lwr.Item(i).Value, 1) = "A" Then
            lwr.Item(i).EntireRow.copy
            Sheets("OTi").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            ActiveCell.Offset(1, 0).Select
            Sheets("GAWi").Select
            lwr.Item(i).EntireRow.Delete
    End If   Next i

Leave a Reply

Your email address will not be published. Required fields are marked *