Ron de Bruin has already written sample code for this:

http://www.rondebruin.nl/copy5.htm#all

--
Regards,
Tom Ogilvy


"DejaVu" wrote in
message ...

In one column, there are values like:
Andy
Bob
Cindy
Dan
Ed
etc...

I'm wanting some sort of procedure to go through this column, find how
many variations there are and store that number as a variable. Also,
I'd like to be able to store all of the string values into an array to
be used later. For example; if the above was my data, there would be
five variations (Andy, Bob, Cindy, Dan, Ed).
Then use that array to take all the rows that have "Andy" in them and
move them to a sheet that will be created named "Andy". And loop
through each name.

I've worked on a somewhat similar project before, but I'm having
trouble figuring this one out. Here is the code I used before that
would look for one word and move all those rows to another column.
This is code that was previously suggested to me on this message board
(Thanks Jim Thomlinson!). 'Link'
(http://www.excelforum.com/showthread.php?t=385313)

Sub CopyCells()
Dim rngFirst As Range, rngCurrent As Range, rngFoundCells As Range
Dim rngToSearch As Range, rngToPaste As Range
Dim wksToSearch As Worksheet, wksToPaste As Worksheet

Set wksToSearch = Sheets("Rough")
Set wksToPaste = Worksheets.Add(, Sheets.Count)
Set rngToSearch = wksToSearch.Cells
Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0)
Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole)

If rngCurrent Is Nothing Then
MsgBox strWordToFind & " was not found"
Else
Set rngFirst = rngCurrent
Set rngFoundCells = rngCurrent.EntireRow
Do
Set rngFoundCells = Union(rngCurrent.EntireRow,
rngFoundCells)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngFirst.Address = rngCurrent.Address
rngFoundCells.Copy rngToPaste
rngFoundCells.Delete
End If

End Sub

TIA for all help!

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=389078