You could do a loop through your sheets in the Workbook_Open function

Dim i as Long, targetName as String

For i = 1 to Sheets.Count
targetName = Sheets(i).Name
Cell("A2").Value = targetName
Workbooks("Book2").Sheets("Record").Columns("A:B") .AdvancedFilter
Action:= _
xlFilterCopy, CriteriaRange:=Range("A1:A2"),
CopyToRange:=Range("A4"), _
Unique:=False
Next i

So here, Cell A1 would have the Column Name where all your name records
are kept, and Cell A2 would be set to the name of the sheet (and thus
the name of the person.) The filter would then copy all the records in
Columns A & B (Name and Account) that have that name, and copy it to
Cell A$ on that name's sheet.

Since you said that you have first initials with the last names, you
may need to create a column that is just Last names,
(=left(a1,len(a1)-2)) or rename your sheets to include first initials.


--
cmk18
------------------------------------------------------------------------
cmk18's Profile: http://www.excelforum.com/member.php...fo&userid=6047
View this thread: http://www.excelforum.com/showthread...hreadid=386538