• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

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

0
jfrank85
Asked:
jfrank85
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
jfrank85Author Commented:
works great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now