Sort Current Worksheet

M

MiataDiablo

VBA newbie. I'm sure there's an easier way to write this but I can't
seem to form my question succinctly enough to find the answer.

My code below works perfectly. Except for the fact that my workbook
contains 25 worksheets that I want to sort exactly the same way and
only 3 worksheets I need to sort differently. How can I update the
first argument to say "If ActiveWorksheet.Name <> ("Sheet1, Sheet2,
Sheet3, Sheet4, etc. all the way to 22), sort this way; if Active
Worksheet.Name = "Sheet23", sort this way.

Any and all help is tremedously appreciated.

Sub SortCurrent()
If ActiveSheet.Name = "Sheet1" Then

Range("A:L").Sort Key1:=Range("C2"), Order1:=xlAscending,
Key2:=Range _
("D2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End If

If ActiveSheet.Name = "Sheet 23" Then

Range("B:C").Sort Key1:=Range("B2"), Order1:=xlAscending,
Key2:=Range _
("C2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End If
End Sub
 
B

Bob Phillips

If ActiveWorksheet.Name = "Sheet23" Or _
ActiveWorksheet.Name = "Sheet24"Or _
ActiveWorksheet.Name = "Sheet25" Then

SortOtherWay
Else

SortThisWay
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

Roger Govier

Hi

Maybe something like the following

If activesheet.index <23

sort1

Else

sort2

End if
 
M

MiataDiablo

Hi

Maybe something like the following

If activesheet.index <23

        sort1

Else

    sort2

End if

--
Regards
Roger Govier












- Show quoted text -

I went with Bob's suggestion (because my sheets aren't really named
Sheet1,2,3, etc. I just used that for clarification purposes) but I
receive a "Too many continuation lines" error message.

Thanks as always
 
D

Dave Peterson

Maybe using "Select Case" and the short list of worksheet names:

Add
Option Compare Text
at the top of your module or you'll have to be very careful matching the case of
the sheet names in your code.

Sheet1 <> SHEET1 <> sheET1 <> ShEeT1

Select case activesheet.name
case is = "sheet1", "sheet2", "sheet99"
call doOneWay
case else
call dotheotherway
end select
 
B

Bob Phillips

Note I went inverted to your way, I cheked the ones that did match, so as to
reduce the list. If the list is smaller the other way, use

If ActiveWorksheet.Name <> "Sheet1" And _
ActiveWorksheet.Name <> "Sheet2" And _
ActiveWorksheet.Name <> "Sheet3" Then

SortThisWay
Else

SortOtherWay
End If


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi

Maybe something like the following

If activesheet.index <23

sort1

Else

sort2

End if

--
Regards
Roger Govier












- Show quoted text -

I went with Bob's suggestion (because my sheets aren't really named
Sheet1,2,3, etc. I just used that for clarification purposes) but I
receive a "Too many continuation lines" error message.

Thanks as always
 
M

MiataDiablo

Note I went inverted to your way, I cheked the ones that did match, so as to
reduce the list. If the list is smaller the other way, use

    If ActiveWorksheet.Name <> "Sheet1" And _
        ActiveWorksheet.Name <> "Sheet2" And _
        ActiveWorksheet.Name <> "Sheet3" Then

        SortThisWay
    Else

        SortOtherWay
    End If

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







I went with Bob's suggestion (because my sheets aren't really named
Sheet1,2,3, etc. I just used that for clarification purposes) but I
receive a "Too many continuation lines" error message.

Thanks as always- Hide quoted text -

- Show quoted text -

Both of you are brilliant, brilliant, brilliant. I knew it would be
easy. I couldn't see the forest for the trees. Thank you all, have a
great weekend!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top