how to filter/move rows based on worksheet names?

[blist]

My spreadsheet has 10 worksheets named A-J. In worksheet A, my entire inventory is posted to a thousand+ rows, with all entries in Col 1 containing the same A thru J data. A-J names will not change.

What is the easiest way to move entire rows from worksheet A to worksheets B-J where Col 1 data = worksheetsheet name? Some Col 1 data on worksheet A already matches the sheetname A, and does not need to be moved.

 

[texasalynn]

would there be a chance that the item on sheet A could already be on it's assignment sheet? So would you overwrite the line or just append the new line?

 

[texasalynn]

does this work?

Sub SheetA_J()
Dim LR As Long
Dim Sht As String
Application.DisplayAlerts = False
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    Sht = Cells(i, 1)
    If Sht <> "A" Then
            Rows(i).Cut Destination:=Sheets(Sht).Range("A" & Rows.Count).End(xlUp) + 1
    End If
Next i
MsgBox "Done"
Application.DisplayAlerts = True
End Sub

note: this is untested

 

[blist]

Thanks for your reply!

When I run the code, it shows a Type mismatch error at this line:
Rows(i).Cut Destination:=Sheets(Sht).Range("A" & Rows.Count).End(xlUp) + 1

 

[texasalynn]

ok so I tested and made this change

Rows(i).Cut Destination:=Sheets(Sht).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Range("A1")

 

[blist]

texaslynn, Thanks again. I made a minor adjustment on this line and now it works as i need.

            Rows(i).Cut Destination:=Sheets(Sht).Range("A" & Rows.Count).End(xlUp).Range("A2")