Macro to group sheet by certain letters at the end

B

bioyyy

Hi:

I'd like you to help me with grouping worksheets. if the sheets contain
letter of "IS" at the end of the tab name, group them together.

For example, abc_IS, test1_IS, test2-IS.

So, If the sheets contain IS at the end of the tab name, group them
together, and move them first. Thanks for your help!
 
S

Sheeloo

Use the macro
Sub movesheets()
For Each ws In Worksheets
If (Right(ws.Name, 2) = "IS") Then
ws.Move _
before:=Worksheets(1)
End If
Next

If case is not important then change ws.Name to UCase(ws.name) in the macro
 
B

bioyyy

Sheeloo,

I have a questions. It works fine if I put it in the module 1. However, when
I put it in the macro toolbar, I got an error in this line:

For Each ws in Worksheets (it highlight ws in blue color).

Any suggestions. Thanks,
 
S

Sheeloo

replace
For Each ws In Worksheets
with
For Each ws In ActiveWorkbook.Worksheets

and
before:=Worksheets(1)
with
before:=ActiveWorkbook.Worksheets(1)

I have not tested it but this should work. You basically need to pass the
activeworkbook when invoking from toolbar...
 
B

bioyyy

Sheelo:

I fix, I just added this line:

Dim ws As Worksheet

BTW, thanks for all your help.
 
S

Sheeloo

How are you assigning to the macro toolbar?
I put it in my personal.xls and attached the macro to a button. It worked
fine without any modification.
Do you have option Explicit set?

Try the following;

Sub movesheets()
'added declaration
Dim ws As Worksheet
For Each ws In Worksheets
If (Right(ws.Name, 2) = "IS") Then
ws.Move _
before:=Worksheets(1)
End If
Next
End Sub
 
Top