Macro to group sheet by certain letters at the end
 

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!

Macro to group sheet by certain letters at the end
 

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

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

"bioyyy" wrote:

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!

Macro to group sheet by certain letters at the end
 

Sheeloo:

Thanks for your help. It works great.



"Sheeloo" wrote:

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

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

"bioyyy" wrote:

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!

Macro to group sheet by certain letters at the end
 

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,


"Sheeloo" wrote:

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

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

"bioyyy" wrote:

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!

Macro to group sheet by certain letters at the end
 

Sheelo:

I forgot, the error is WS (variable is not defined). Thanks

"Sheeloo" wrote:

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

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

"bioyyy" wrote:

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!

Macro to group sheet by certain letters at the end
 

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

and
befo=Worksheets(1)
with
befo=ActiveWorkbook.Worksheets(1)

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


"bioyyy" wrote:

Sheelo:

I forgot, the error is WS (variable is not defined). Thanks

"Sheeloo" wrote:

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

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

"bioyyy" wrote:

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!

Macro to group sheet by certain letters at the end
 

Sheelo:

I try but still got an compile error: variable not defined for ws. Thanks,

"Sheeloo" wrote:

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

and
befo=Worksheets(1)
with
befo=ActiveWorkbook.Worksheets(1)

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


"bioyyy" wrote:

Sheelo:

I forgot, the error is WS (variable is not defined). Thanks

"Sheeloo" wrote:

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

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

"bioyyy" wrote:

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!

Macro to group sheet by certain letters at the end
 

Sheelo:

I fix, I just added this line:

Dim ws As Worksheet

BTW, thanks for all your help.



"Sheeloo" wrote:

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

and
befo=Worksheets(1)
with
befo=ActiveWorkbook.Worksheets(1)

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


"bioyyy" wrote:

Sheelo:

I forgot, the error is WS (variable is not defined). Thanks

"Sheeloo" wrote:

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

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

"bioyyy" wrote:

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!

Macro to group sheet by certain letters at the end
 

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 _
befo=Worksheets(1)
End If
Next
End Sub


"bioyyy" wrote:

Sheelo:

I fix, I just added this line:

Dim ws As Worksheet

BTW, thanks for all your help.



"Sheeloo" wrote:

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

and
befo=Worksheets(1)
with
befo=ActiveWorkbook.Worksheets(1)

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


"bioyyy" wrote:

Sheelo:

I forgot, the error is WS (variable is not defined). Thanks

"Sheeloo" wrote:

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

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

"bioyyy" wrote:

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!