Link to home
Create AccountLog in
Avatar of jfrank85
jfrank85

asked on

remove workbook from sort vba

hi,

I have a macro that sorts the worksheets within excel for me. I have it excluding any worksheet that starts with "filtered from". I also want to add into the code to exclude any workbook called "RUN". How can I include that in the below?


Private Sub Sort_Active_Book()
Dim I As Integer
Dim J As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = vbYes
'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
        
        ' Exclude certain sheets from Sorting
        If InStr(1, LCase(Sheets(J).Name), "filtered from") = 0 Then
        
            ' 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
        End If
    Next J
Next I
End Sub

Open in new window

Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Change the line no-17 in your code which is this...

If InStr(1, LCase(Sheets(J).Name), "filtered from") = 0 Then

Open in new window


To this...

  If InStr(1, LCase(Sheets(J).Name), "filtered from") = 0 And InStr(1, LCase(Sheets(J).Name), "Run") = 0 Then

Open in new window


Saurabh...
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try
Private Sub Sort_Active_Book()
Dim I As Integer
Dim J As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = vbYes
'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
        
        ' Exclude certain sheets from Sorting
        If InStr(1, LCase(Sheets(J).Name), "filtered from") = 0 Or _
            LCase(Sheets(J).Name) <> "run" Then
        
            ' 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
        End If
    Next J
Next I
End Sub

Open in new window

Regards
And my bad since you have it lowercase it will be like this...

 If InStr(1, LCase(Sheets(J).Name), "filtered from") = 0 And InStr(1, LCase(Sheets(J).Name), "run") = 0 Then

Open in new window


or you can ignore the cases like this is even more efficient way...

  If InStr(1, Sheets(J).Name, "filtered from", vbTextCompare) = 0 And InStr(1, Sheets(J).Name, "Run", vbTextCompare) = 0 Then

Open in new window


Saurabh...
Since you said workbook:

Private Sub Sort_Active_Book()
Dim I As Integer
Dim J As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'

If UCase$(Left$(Activeworkbook.name, 3)) = "RUN" then exit sub
iAnswer = vbYes
'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
        
        ' Exclude certain sheets from Sorting
        If InStr(1, LCase(Sheets(J).Name), "filtered from") = 0 Then
        
            ' 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
        End If
    Next J
Next I
End Sub

Open in new window

Avatar of jfrank85

ASKER

Rgonzo1971, Saurabh Singh Teotia - tried your suggestions and didn't ignore those sheets from the sort.

Rory, sorry i meant to say worksheet: "RUN"

so I have three worksheets that I don't want included in the sort

"RUN"
"Filtered from Prev Year"
"Filtered from Current Year"
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
works great, thanks!