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

jfrank85Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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...
0
Rgonzo1971Commented:
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
0
Saurabh Singh TeotiaCommented:
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...
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rory ArchibaldCommented:
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

0
jfrank85Author Commented:
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"
0
Saurabh Singh TeotiaCommented:
jfrank85..

Did you tried this one..as this should take care of it...

 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...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jfrank85Author Commented:
works great, thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.