moving rows between sheets

date	call	info									lead

Hello,
I'm a real newb and any help would greatly appreciated.
I have 3 sheets that contains contact info. named "hot" "cold" and "lost".
I would like it so that when "cold" is entered in the "lead" column, the row moves to the "cold" sheet and if "lost" is entered moves to "lost" sheet, if nothing is entered the row stays on sheet. The same thing for the other 2 sheets.
There are 12 columns, lead is col. "L" and row "1" is header.
I tried this code on hot sheet, but when it runs I get: run time error "9", subscript out of range.
Any help would greatly appreciated, thanks
Code:

Private Sub Worksheet_Change(ByVal Target As Range) Dim lr As Long With Target If .Rows.Count = 1 And .Column = Columns("L").Column Then If LCase(.Value) = "cold" Or LCase(.Value) = "lost" Then .EntireRow.Copy lr = Worksheets(.Value).Cells(Rows.Count, "L").End(xlUp).Row Worksheets(.Value).Paste Worksheets(.Value).Cells(lr + 1, "A") Application.EnableEvents = False .EntireRow.Delete Application.EnableEvents = True End If End If End With End Sub

Hi,
Check if this can help you out:
httppp://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/

(delete the 2 "p"s after http as I can't post links yet)
Cheers!

Thanks,

I have 2 questions;
1- do the cells have to be the same size on all the sheets-some have been resized depending on info entered?
2- will the paste func put it the first empty row on the other sheets?

I have attached the wb-after deleting personal info

@ dpotter13,

Your code is referring to your sheets as "cold" and "lost" instead of "Cold Leads" and "Lost Leads".
Remove the space and Leads from the actual sheet names and your code executes as expected.

Thank you so much..worked perfectly

Update..worked in test WB but not in real WB. I renamed sheets and copied code...error 9 comes up...thoughts

Nevermind..forgot to rename in ALL lower case