Excel 2007 : Sort Sheet alphabetically.

**GaidenFocus** · 12-06-2010, 04:03 PM

I need to know how to sort my tabs or sheets. the thing is i have a code that sorts ALL of my sheets alphabetically, but i need this macro to leave the others as is, and just sort the new sheet.

This is my create sheet macro, it works great, but it throws it at the end of my workbook. i need it to put it alphabetically with the others.

Sub createsheet()
Dim SearchData As String
Application.ScreenUpdating = False

SearchData = InputBox("Enter the tag number.")

Sheets("NEW").Copy Before:=Sheets("NEW")
Sheets("NEW (2)").Activate
ActiveSheet.Name = SearchData
Sheets("INVENTORY").Activate
    Range("B5").Select
    Selection.AutoFill Destination:=Range("B5:B731"), Type:=xlFillDefault
    Range("B5:B731").Select
Sheets("autoship").Activate
End Sub

this is my sort sheets macro, but i dont want to do this every time i create one single sheet as it takes about 10 minutes for this macro to finish.

Sub SortSheets()
Dim i, j As Integer
Dim iNumSheets As Integer

iNumSheets = ActiveWorkbook.Sheets.Count

Application.ScreenUpdating = False
For i = 1 To iNumSheets - 1
For j = i + 1 To iNumSheets
If UCase(Sheets(i).Name) > UCase(Sheets(j).Name) Then
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
Application.ScreenUpdating = True
End Sub

thanks in advance

**JBeaucaire** · 12-06-2010, 06:42 PM

Maybe this:

Option Explicit

Sub CreateSheet()
Dim SearchData As String
Dim ws As Worksheet
Application.ScreenUpdating = False

SearchData = InputBox("Enter the tag number.")

Sheets("NEW").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = SearchData
Sheets("INVENTORY").Activate
    Range("B5").AutoFill Destination:=Range("B5:B731"), Type:=xlFillDefault
    Range("B5:B731").Select
Sheets("autoship").Activate

'move sheet alphabetically
    For Each ws In Worksheets
        If ws.Name <> SearchData And Sheets(ws.Index - 1).Name < SearchData Then _
                Sheets(SearchData).Move Before:=ws
    Next ws
    
Application.ScreenUpdating = True
End Sub

**GaidenFocus** · 12-06-2010, 06:58 PM

I'm getting a subscript out of range error. =\

**tigertiger** · 12-06-2010, 07:52 PM

Originally Posted by GaidenFocus

I'm getting a subscript out of range error. =\

the range may be merged cell , plz check it again

**JBeaucaire** · 12-06-2010, 07:09 PM

Since it works for me, I'd have to see your workbook to understand the problem.

**GaidenFocus** · 12-08-2010, 12:37 PM

yea i can't post the workbook, it contains alot of sensitive information.

this is the line its giving me the error with.

If ws.Name <> SearchData And Sheets(ws.Index - 1).Name < SearchData Then Sheets(SearchData).Move Before:=ws

**JBeaucaire** · 12-08-2010, 02:27 PM

Since all we're working on now is sorting by sheet name, post your workbook without the data in it. We can test just that part on your actual sheet names.