ExcelBanter - Adding and naming new worksheets

Hi Byron,

You could extract the unique values to another location, using the advanced
filter feature, and then run Rowan's code on the extracted unique data.

Alternatively, try:

Assume the category names are in column A and tha row 1 is a header row.

With minor adaptation
'===========================
Sub Tester99()
Dim i As Long, j As Long
Dim WB As Workbook
Dim WS As Worksheet
Dim SH As Worksheet
Dim Rng As Range, Rng1 As Range
Dim rCell As Range
Const col As String = "A" '<<=========== CHANGE

Set WB = ActiveWorkbook '<<=========== CHANGE
Set SH = WB.ActiveSheet '<<=========== CHANGE

i = SH.Cells(Rows.Count, col).End(xlUp).Row

Set Rng = SH.Range("A1").Resize(i)

Rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

On Error Resume Next
Set Rng1 = Rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

For j = 2 To Rng1.Cells.Count
Set WS = Sheets.Add(after:=WB.Sheets(WB.Sheets.Count))
WS.Name = Rng1(j).Value
Set WS = Nothing
Next j

SH.ShowAllData

End Sub
'<<===========================

---
Regards,
Norman

"Byron" wrote in message
...
I have a worksheet that has 200-300 rows each falling into one of 8-10
categories. I am trying to find a way to create new worksheets for each
of
the 8-10 categories, and then name them from the column that these
catefories
are located.

Rowan from another post suggested:

One way would be to use a macro. If you have a unique list of categories
in
column A then something like this should work:

Sub lime()
Dim i As Long
Dim ws As Worksheet
Dim tSht As Worksheet
On Error Resume Next
Set tSht = ActiveSheet
For i = 1 To tSht.Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Sheets.Add
ws.Name = tSht.Cells(i, 1).Value
Set ws = Nothing
Next i
On Error GoTo 0
End Sub

I thing this will work, I just need a way to tell this macro to only pick
one of each of the categories in the column and name a new worksheet.

Any ideas?

Thanks,
Byron