1004 Error when sorting worksheet tabs

[Kaitlyn]

I have a workbook where I have created a Table of Contents with hyperlinks to the worksheets. I sort them alphabetically, but I am suddenly getting a 1004 error.

This is the code (which I copied from elsewhere). It works just fine in another workbook, so I copied it to this workbook, when I began getting 1004 errors.

Anyway, what am I doing wrong? If I want to NOT sort descending, do I just delete that code ... and where?


Sub mcr_Workbook_Sort()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
Sheets("1_GO_TO").Select

End Sub

 

[StephenCrump]

Welcome to the Forum

Are you sure it's this code, and not other code, producing the error?

Which line is the problem, and what is the actual error message you are getting?

 

[Yongle]

Welcome to the forum

Have a look at the Review Tab
- is the workbook protected ?
- that would cause 1004 Error


Using code tags when posting codes makes it much easier to read as you can see below

Sub mcr_Workbook_Sort()
    Dim i As Integer, j As Integer
    Dim iAnswer As VbMsgBoxResult

' Prompt the user which direction they wish to sort the worksheets.

    iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
        & "Clicking No will sort in Descending Order", _
        vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")

    For i = 1 To Sheets.Count
        For j = 1 To Sheets.Count - 1

'If the answer is Yes, then sort in ascending order.
            If iAnswer = vbYes Then
                If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then Sheets(j).Move After:=Sheets(j + 1)

'If the answer is No, then sort in descending order.

            ElseIf iAnswer = vbNo Then
                If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then Sheets(j).Move After:=Sheets(j + 1)
            End If
        Next j
    Next i

'Sheets("1_GO_TO").Select
Sheets(1).Select
End Sub

 

[ajetrumpet]

a couple of relevant pieces of info can be found here: Excel VBA run-time error 1004 : Application-defined or object-defined error

 

[Kaitlyn]

Welcome to the Forum

Are you sure it's this code, and not other code, producing the error?

Which line is the problem, and what is the actual error message you are getting?

 

[Kaitlyn]

The error is showing up here in the ascending sort ...

Sheets(j).Move After:=Sheets(j + 1)

Interestingly, when I click no and sort descending, there is no problem

 

[Kaitlyn]

Move method of worksheet class failed is the error message with 1004.

 

[Kaitlyn]

Welcome to the forum

Have a look at the Review Tab
- is the workbook protected ?
- that would cause 1004 Error


Using code tags when posting codes makes it much easier to read as you can see below

Sub mcr_Workbook_Sort()
    Dim i As Integer, j As Integer
    Dim iAnswer As VbMsgBoxResult

' Prompt the user which direction they wish to sort the worksheets.

    iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
        & "Clicking No will sort in Descending Order", _
        vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")

    For i = 1 To Sheets.Count
        For j = 1 To Sheets.Count - 1

'If the answer is Yes, then sort in ascending order.
            If iAnswer = vbYes Then
                If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then Sheets(j).Move After:=Sheets(j + 1)

'If the answer is No, then sort in descending order.

            ElseIf iAnswer = vbNo Then
                If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then Sheets(j).Move After:=Sheets(j + 1)
            End If
        Next j
    Next i

'Sheets("1_GO_TO").Select
Sheets(1).Select
End Sub

How do you do this in color. I'm a VBA newbie.

 

[Kaitlyn]

How do you do this in color. I'm a VBA newbie.

Oh - I missed. Right now the workbook is not protected, but it was working on the protected workbook last time I had it open. I removed a couple worksheets and added a couple more. Then it stopped working - ascending only Descending works fine. So odd.

 

[StephenCrump]

If your code worked when the workbook was protected, it must mean that the sheets were sorted correctly at the time, and hence the code wasn't trying to move any sheets. But sorting the other way (i.e. ascending instead of descending in your case) would necessarily throw an error.

Is your code still not working on an unprotected workbook? If so, another possibility is that you have very hidden worksheets.

In the VB editor, can you see any sheets that aren't visible in Excel? Are you able to unhide these sheets from within Excel?