Compare columns in worksheets and move to correct sheet.

[CRIMEDOG]

I have attached a file.
Sheet1 has a list of equipment with serial numbers in column A.
I need to Loop through each piece of equipment and compare the Serial number to each Named Range (CompanyA) & (CompanyB) - the serial number MAY be in CompanyA Or CompanyB or neither.
If there is a match, I would like to move the entire row to the first open row in the corresponding sheet. If no match, move to the next row.

Some heavy lifting is done (I hope) the macro in Module1 resizes the range on sheet1 and begins a loop at the first row...
I figure it would be easiset with Case Statements (because in the real workbook there will be about 15 tabs)
Thank you for looking at this.

[pike]

Hi CRIMEDOG
one of many ways

Please Login or Register  to view this content.

[CRIMEDOG]

That Works great!
One more step - how would I have it remove the lines from Sheet1 after moving to destination - Instead of Copy, Cut & paste
Or cycle through one more time and delete...

[CRIMEDOG]

I tried copying the macro and modifying it to delete the row that match instead of copying

Please Login or Register  to view this content.

Getting error : Oblect variable or With Block variable not set
on line

Please Login or Register  to view this content.

[CRIMEDOG]

Here is the file so far

Any help would be great.

[pike]

little different...

Please Login or Register  to view this content.

[pike]

Hi CRIMEDOG

If you have many sheets it would be better to recycle the find code using arrays

Please Login or Register  to view this content.

[CRIMEDOG]

Works like a charm - the Array is what I am using
Thanks