| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| list sheet 1 - same cell (eg A1) in a group of worksheets sheet | Excel Worksheet Functions | |||
| How can I convert a group of numbers to a group of letters? | Excel Worksheet Functions | |||
| Deleting a space between a group of Numbers & Letters in a cell | New Users to Excel | |||
| How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions | |||
| Using a macro how do I group every sheet within a book? | Excel Discussion (Misc queries) | |||
Hybrid Mode
