Thanks a ton.

Really appreciate that. did exactly what i wanted.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Ooops, should have changed one other thing:

Sub ExportSheetsFromDatabase()
'Based on the value in column R
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim FieldNum As Integer

Set myArea = Range("R2").CurrentRegion

Set myArea = Intersect(Range("R:R"), myArea.Offset(1,
0).Resize(myArea.Rows.Count - 1))
FieldNum = 19 - Range("R2").CurrentRegion.Columns(1).Column
For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=FieldNum, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub


--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Dominique,

Try the sub below: assumes that data in column R starts in R2, and the
first row is labels.

HTH,
Bernie
MS Excel MVP


Sub ExportSheetsFromDatabase()
'Based on the value in column R
Dim myCell As Range
Dim mySht As Worksheet
Dim myName As String
Dim myArea As Range
Dim FieldNum As Integer

Set myArea = Range("R2").CurrentRegion

Set myArea = Intersect(Range("R:R"), myArea.Offset(1,
0).Resize(myArea.Rows.Count - 1))
FieldNum = 19 - ActiveCell.CurrentRegion.Columns(1).Column
For Each myCell In myArea
On Error GoTo NoSheet
myName = Worksheets(myCell.Value).Name
GoTo SheetExists:
NoSheet:
Set mySht = Worksheets.Add
mySht.Name = myCell.Value
With myCell.CurrentRegion
.AutoFilter Field:=FieldNum, Criteria1:=myCell.Value
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With
Resume
SheetExists:
Next myCell
End Sub




"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