Hi,

I am working with 2 workbooks.

The first workbook has a list of worksheets in alphabetical order by
last name, for example:
"Anderson", "Casey", "Lee", etc.

The second workbook has a sheet with a list of names as well as their
account numbers, descriptions and end dates. However, each person has
several account numbers, and the names in this workbook includes the
Last Name as well as the First Initial. For example, "Anderson M",
"Lee, F", etc.

How can I get vba to sort it so that I can filter out the names and
copy the necessary rows to their respective sheets in workbook1?
Desperately in need of help. Thanks in advance!


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile: http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=386538

If you want to jst do this once, you can use the Advanced Filter utility
under Data/Filter. You would select the rows in which the data exist on
sheet2, select copy to location, and also unique records only. If you
need it to happen dynamically, you would put this code in vba.


Range("$A:$A").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheet1.Range("A1"), Unique:=True

You would probably want to put this in the Sheet2 Deactivate sub
function.

Tip: If you know how to do something with workbook functions or
utilities, but don't know how to do it in VBA using the macro recorder
can help you determine the syntax needed. Simply turn ont he macro
recorder and do what you want vba to do, and you'll have the code
(though not always as clean as you might like it to be)


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

thanks for the reply. My biggest concern is how to filter it so that the
criteria matches the name on the sheets in workbook1. Each sheet has its
own name "Anderson, Casey, Harold, Lee, etc.". How do I filter the names
in workbook2 so that it would copy the account numbers that match the
names of the sheets in worksheet1.

For example, the sheet1 is named "Anderson".
I want to code it in vba so that it would filter out "Anderson" and
then copy the rows to sheet "Anderson".

For "Lee", I want it to filter out only "Lee" or something containing
"Lee" and copy the rows into the respective worksheets in workbook1. If
this is confusing, let me know. I'm trying my best to explain the
situation. Thanks again


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile: http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=386538

An easy way (to me) would be to copy the name column to a dummy column
Than use Data Text to Columns to separate the last name from the initials.
Loop through this new name column and build a data transfer macro.
If all the pages in WB1 have sheets with the last name of person, than if
WB2.Cell(tested) = Nm
WB1.Sheets(Nm) represents the worksheet to transfer to

than it is just a matter of identifying which row & column to put the info
in

For x = 1 to lastrow(WB2)
WB1.Sheets(CellText).Cells(rw,col) =
WB2.Sheets("DataSheet").Cells(x,col1)
next

cycle through all the columns in WB2 or change Cells(x,col1) to
Range(cells(x,1),cells(x,5)

--
steveB

Remove "AYN" from email to respond
"Sethaholic" wrote
in message ...

Hi,

I am working with 2 workbooks.

The first workbook has a list of worksheets in alphabetical order by
last name, for example:
"Anderson", "Casey", "Lee", etc.

The second workbook has a sheet with a list of names as well as their
account numbers, descriptions and end dates. However, each person has
several account numbers, and the names in this workbook includes the
Last Name as well as the First Initial. For example, "Anderson M",
"Lee, F", etc.

How can I get vba to sort it so that I can filter out the names and
copy the necessary rows to their respective sheets in workbook1?
Desperately in need of help. Thanks in advance!


--
Sethaholic
------------------------------------------------------------------------
Sethaholic's Profile:
http://www.excelforum.com/member.php...o&userid=25113
View this thread: http://www.excelforum.com/showthread...hreadid=386538

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