Lead Organizer

I want a main sheet where all of my business leads are...then I want a tab for each account executive. Depending on who's name is in the cell on sheet 1, all the lead info will be transferred to their tab. And I want all the leads sent to Monthly tabs as well...so all the leads with a date in May get sent to the May tab, etc.

This is what I tried to do...and it kept saying I had a Wrong Name Somewhere...

1) Select sheet 2010, Insert, Name, Define, Name:DataRng, Refers To: =OFFSET('2010'!$A$3,0,0,COUNTA('2010'!$A:$A)-1,21), Add, Close.

2) Select sheet Greg Leads.

3) Made sure that the headings on Greg Leads in Range A3:T3 are EXACTLY the same as the headings on sheet 2010.

4) Right click on the tab, select view code and enter the code
Code:
Private Sub Worksheet_Activate()
Range("M1").Value = Sheets("2010").Range("A3").Value
Range("M2").Value = "Greg"

Sheets("2010").Range("datarng").AdvancedFilter action:=xlFilterCopy, criteriarange:=Range("M1:M2"), copytorange:=Range("A3:T3")

Range("M1:M2").ClearContents

End Sub

BUT THIS DIDN'T WORK...I thought I did that right...maybe I had a typo I can't find??


And this is what I did to try and move all the info for the houses that were auctioning in May...Code for May Auction tab....

Code:
Private Sub Worksheet_Activate()
Range("M1").Value = Sheets("2010").Range("B3").Value
Range("N1").Value = Sheets("2010").Range("B3").Value
Range("M2").Value = ">=5/1/10"
Range("N2").Value = "<=5/31/10"

Sheets("2010").Range("datarng").AdvancedFilter action:=xlFilterCopy, criteriarange:=Range("M1:N2"), copytorange:=Range("A3:T3")

Range("M1:N2").ClearContents

End Sub


What am I doing wrong???? I think I am doing the datarng part wrong.

This file was just an example...my actual file is too large to attach. The sheet is called 2010 in my file.

I have this exact same type worksheet for my other business and it works great, but when i tried to create this file it doesn't work. This is the file I originally created that worked.

btw...I should mention - I do not know too much about writing codes....I was an engineer by schooling so I took some C and C+ writing, but not much of it stuck. I had help with the ACS file and I dont fully understand the code, which is why I'm having a hard time converting it to my new file.

This is the file that I am trying to make....however, in addition to "Greg Leads" as the first tab; I also need "Luis Leads" and "Aaron Leads"...then depending on whose name is on Sheet 1 "2010 Leads", all of that info will transfer onto the appropriate tab.

Then of course whatever date the auction date is will transfer the info into the correct monthly tab.

I am having the hardest time uploading this file...it's only 799kb.

Anybody help?????

I am working in excel 2007 and it doesnt look familiar at all to me

It is only copying the information through the L column...then starting back over copying the information from the A column into the M column and saying there's an error. I am dying over here.

Solved it myself!!!

Thanks though!!!