sorting many worksheets

[daveturner80]

I have a workbook with 7 sheets in it. I am a member of a fire dept. and I use Col. A as our members names, last name,first name. When one of our female members gets married and changes her last name to her husband's last name,
I need to change her name and sort all the sheets. I am using excel 2010 and would like to know if this is possible to do? I need it to be able to sort all the columns in all the sheets.
Thanks,
Dave

 

[Sektor]

Sub SortSheets()

' This routine sorts the sheets of the
' active workbook in ascending order.

    Dim SheetNames() As String
    Dim SheetHidden() As Boolean
    Dim i As Integer
    Dim SheetCount As Integer
    Dim VisibleWins As Integer
    Dim Item As Object
    Dim OldActive As Object
    
    If ActiveWorkbook Is Nothing Then Exit Sub 'No active Workbook
    SheetCount = ActiveWorkbook.Sheets.Count
    
    ' Check for protected workbook structure
    If ActiveWorkbook.ProtectStructure Then
        MsgBox ActiveWorkbook.Name & " is protected .", vbCritical, "Cannot Sort Sheets."
        Exit Sub
    End If
    
    ' Disable Ctrl+Break
    Application.EnableCancelKey = xlDisabled
    
    ' Get the number of sheets
    SheetCount = ActiveWorkbook.Sheets.Count
    
    ' Redimension the arrays
    ReDim SheetNames(1 To SheetCount)
    ReDim SheetHidden(1 To SheetCount)
    
    ' Store a reference to the active sheet
    Set OldActive = ActiveSheet
    
    ' Fill array with sheet names
    For i = 1 To SheetCount
        SheetNames(i) = ActiveWorkbook.Sheets(i).Name
    Next i
    
    ' Fill array with hidden status of sheets
    For i = 1 To SheetCount
        SheetHidden(i) = Not ActiveWorkbook.Sheets(i).Visible
        ' Unhide hidden sheets
        If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = True
    Next i
    
    ' Sort the array in ascending order
    Call BubbleSort(SheetNames)
    
    ' Turn off screen updating
    Application.ScreenUpdating = False
    
    ' Move the sheets
    For i = 1 To SheetCount
        ActiveWorkbook.Sheets(SheetNames(i)).Move Before:=ActiveWorkbook.Sheets(i)
    Next i
    
    ' Re-hide sheets
    For i = 1 To SheetCount
        If SheetHidden(i) Then ActiveWorkbook.Sheets(i).Visible = False
    Next i
    
    ' Reactivate the original active sheet
    OldActive.Activate
    
End Sub

Private Sub BubbleSort(List() As String)
    ' Sorts the List array in ascending order
    Dim First As Integer, Last As Integer
    Dim i As Integer, j As Integer
    Dim Temp As String
    
    First = LBound(List)
    Last = UBound(List)
    
    For i = First To Last - 1
        For j = i + 1 To Last
            If List(i) > List(j) Then
                Temp = List(j)
                List(j) = List(i)
                List(i) = Temp
            End If
        Next j
    Next i
    
End Sub

 

[daveturner80]

this doesn't seem to work.
I saved it as a macro and ran it and it didn't do anything.
What can I do now?

 

[Sektor]

If it "didn't do anything", then your sheets seem to be already sorted!
Try to place sheets in random order and run macro again.

 

[daveturner80]

I am not getting your concept of putting the sheets in random order?
What is that going to do with sorting the names in all the sheets that I have in the workbook?
I went into all the sheets and changed my name from Turner,Dave to Miller,Dave and ran the macro and it didn't sort anything.
What am I doing or not doing to get these sheets sorted?
Thanks for all your time.
Dave

 

[daveturner80]

I finally looked and saw that your macro is working correctly, however that isn't what I need to do.
I have a workbook with 7 sheets in it. I am a member of a fire dept. and I use Col. A in all the sheets as our members names, last name,first name. When one of our female members gets married and changes her last name to her husband's last name,
I need to change her name in all the sheets and then sort all the sheets. I am using excel 2010 and would like to know if this is possible to do? I need it to be able to sort all the columns in all the sheets.
I hope this makes sense to you. I don't want to sort the sheets I need to sort the names in each sheet.
thanks,
Dave