Sub States()
Dim sh As Worksheet
Dim stemp As String
Dim rng As Range
Dim i As Long

Set sh = ActiveSheet
Do While Range("A1").Value < ""
stemp = Range("A1")
Set rng = Rows(1)
i = 2
Do While Cells(i, "A") < ""
If Cells(i, "A").Value = stemp Then
Set rng = Union(rng, Rows(i))
End If
i = i + 1
Loop
Worksheets.add.Name = stemp
rng.Copy Worksheets(stemp).Range("A1")
rng.Delete
sh.Activate
Loop

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dominique Feteau" wrote in message
...
Anyone have any suggestions on how I should do this without resorting to
Access?

I have a sheet with Column R representing the State a particular file is
in.
I'll get a file like this every few weeks. I'd like to automate how I
need
it broken down. I'd like to have a new sheet created for each unique
state
(not all 50 states) in Column R. Then have all the rows that in Column R
copied to each sheet that matches the value in that row. For example I
have
20 unique states, create 20 sheets using those values, copy all rows that
match the names of the sheets.

I'm trying to figure it out, but I have a feeling that Access might handle
this idea better. Bosses really dont want to use it though. Let me know.

Thanks