I am using MS Excel 2002 on Windows XP. I have a list of
data in 5 columns.
One worksheet contains all the data for a particular job.
Column D contains
cells from a validation of 66 different items. Depending
on what is in
column D, I would like to copy that entire row to a
worksheet whose tab is
the same name as the cell in column D. Sometimes the
worksheet is in the
same workbook and sometimes in a different workbook.

Example: if D4 contains "blue" then I would like to copy
A4.F4 to the
worksheet named "blue".

Can this be done?

TIA

Hello Janet,

(Columns A thru d is the data)
' Get the information from Sheet
Selection.AutoFilter Field:=1, Criteria1:=Range("Field name in Auto
filter column").Text
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

(column F is where the answer goes)
' Put the information
Range("f1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

This will work for one to many rows of data for the find. And I use the
Auto Filter search and find.

Michael Kintner
CEO, Business Developer
Microsoft Gold Certified
CNerd Inc
mike @ cnerd.com


"JanetP" wrote in message
...
I am using MS Excel 2002 on Windows XP. I have a list of
data in 5 columns.
One worksheet contains all the data for a particular job.
Column D contains
cells from a validation of 66 different items. Depending
on what is in
column D, I would like to copy that entire row to a
worksheet whose tab is
the same name as the cell in column D. Sometimes the
worksheet is in the
same workbook and sometimes in a different workbook.

Example: if D4 contains "blue" then I would like to copy
A4.F4 to the
worksheet named "blue".

Can this be done?

TIA