Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding and naming new worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making for adding and naming a new sheet | Excel Discussion (Misc queries) | |||
Adding and Naming Multiple Worksheets | Excel Worksheet Functions | |||
Naming Worksheets | Excel Programming | |||
Naming and Adding Worksheets | Excel Programming | |||
Quick question about dynamically adding and naming worksheets | Excel Programming |