moving data to another tab in Excel

[hwfan123]

Using the attached file.

I need to move information from column "I" depending on information in the cells in that column.
For example in cell "I5" I would need that entire row moved to the tab marked "Line 3" and deleted from the original location.
Celll "I15" would need to move to the tab marked "line 2" and so on through to the end of the data in column "I".
There are tabs marked Line 1 thru Line 9 and a tab marked "Line HL" and the information in column "I" would need to be sorted into the different tabs.
Anything marked HL03 in column "I" would need to be moved to the "Line HL" tab.
Again when I say moved I mean that the entire row would need to be moved to the correct tab.

Any help is greatly appreciated with this.

[mdmackillop]

Note that Sheet "Line HL" has a space after the "L"

Option Explicit
Sub MoveRows()
Dim i As Long, a, Rw As Long, tgt As Range
Rw = Cells(Rows.Count, 9).End(xlUp).Row
Application.ScreenUpdating = False
For i = 8 To Rw
a = Left(Cells(i, 9), 1)
Select Case a
Case 0 To 9
Set tgt = Sheets("Line " & a).Cells(Rows.Count, 1).End(xlUp)(2)
Rows(i).Cut tgt
Case ""
'do nothing
Case Else
If Cells(i, 9) = "HL03" Then
Set tgt = Sheets("Line HL ").Cells(Rows.Count, 1).End(xlUp)(2)
Rows(i).Cut tgt
End If
End Select
Next
Application.ScreenUpdating = True
End Sub

[mbarron]

With Sign Off Request being your active sheet:

Sub moveLines()
Application.ScreenUpdating = False

Dim i As Long, lRow As Long, strTo As String
lRow = Cells(Rows.Count, 9).End(xlUp).Row
For i = 5 to lRow
If Left(Cells(i, 9), 1) = "H" Then
strTo = "Line HL"
Else
strTo = "Line " & Left(Cells(i, 9), 1)
End If

Cells(i, 1).EntireRow.Cut _
Destination:=Worksheets(strTo).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Next
Application.ScreenUpdating = True

End Sub

[mdmackillop]

With Sign Off Request being your active sheet:


A couple of comments (helpful I hope)

OK on this sheet, but your code will fail on blanks (try testing on a copy of sheet "Line 3")
No need to loop from the bottom with Cut and Paste and it avoids changing the data order.

[mbarron]

A couple of comments (helpful I hope)

OK on this sheet, but your code will fail on blanks (try testing on a copy of sheet "Line 3")
No need to loop from the bottom with Cut and Paste and it avoids changing the data order.

There would be no reason to run it on any sheet other than the Sign off sheet. The other sheets already have the correct info on them.

As far as the backwards loop - yeah I'm so used to the "I want to delete rows based on ..." posts that I did the backwards loops out or habit. (bad habit that it is in this case) I'll fix the code in my post.

[mdmackillop]

You're more trusting than I am.

[hwfan123]

the last one worked very well thanks to you both for helping with this.