Case "Question"

[Pinaceous]

Good Evening,

I have a Case code question.

In having:

         Case "Tab Name"

Now the

"Tab Name"

represents just that, the name at the bottom of the tab of which is the actual name of the worksheet.

Is there anyway to specify this in a generic fashion?

For example with the sheet name?

For example, Sheet1 (Tab Name); somehow using Sheet1 as the name for a Case.

I don't know how to code this.

Please help.

Thank you,
Pinaeous

 

[Michael M]

Something like this ??

For Each ws In Worksheets
 ws.Activate
    Select Case (ws.Name)
           Case "Sheet1"
                'Do Stuff

            Case "Sheet2"
                'Do other stuff
           Case Else
                "Do more stuff
     End Select

 

[Pinaceous]

Hi Michael,

Be patient with me if I confuse it,

In the VBA window, under Microsoft Excel Objects, it lists ..
Sheet1(Name1) - I've named the tab as Name1

So, here the worksheet, ws.Name = Name1 but now if I want with,

Case "Sheet1" meaning the worksheet(1) is this the same as your answer??

 

[Michael M]

Be patient with me if I confuse it,

you confused me...

For Each ws In Worksheets
 ws.Activate
    Select Case (ws.Name)[color=red]'this doesn't change !![/color]
           Case "Name1" [color=red]'this changes to your required sheet name !![/color]
                'Do Stuff

            Case "YOURSHEETNAME"[color=red]'this changes to your required sheet name !![/color]
                'Do other stuff
           Case Else
                "Do more stuff
     End Select

Did that help ??

 

[Pinaceous]

        Select Case UCase(ws.Name)
        
            Case "Sheet1"

What happens if you want …

        Select Case UCase(ws.NUMBER)
        
            Case Worksheets(2)

So, not in using the ws.Name but using its ws.Number.

How do I do this?

& Thank you!

 

[Michael M]

Ahh...you mean like this ??

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
 ws.Activate
    Select Case ActiveSheet.Index
           Case 1 "sheet number
                'Do stuff
            Case 2 'sheet number
               'Do more stuff
     End Select
Next ws
End Sub

 

[Pinaceous]

Hi Michael,

I think that did it for my question.

Thank you,
Paul

 

[Michael M]

Keeping in mind of course that you may / may not need to loop through the sheets.
So, if you want to apply / or not, the code to activesheets it could simply be

Sub MM1()
Select Case ActiveSheet.Index
   Case 1 "sheet number
           'Do stuff
   Case 2 'sheet number
           'Do more stuff
End Select
End Sub

 

[Fluff]

In the VBA window, under Microsoft Excel Objects, it lists ..
Sheet1(Name1) - I've named the tab as Name1

So, here the worksheet, ws.Name = Name1 but now if I want with,

Case "Sheet1" meaning the worksheet(1)

Just a word of warning, Sheet1 is NOT the same as worksheet(1).
Sheet1 is the codename of the sheet & will appear in the Project window in alphabetical order.
Worksheets(1) is the sheet index & refers to the position of sheet in the workbook. So the left most sheet is sheet(1), the next is sheet(2) etc.
Using the index number is unreliable, because if somebody changes the order of the sheets, your code will potentially still work, but on the wrong sheet.

 

[Pinaceous]

Thanks Fluff, I needed that!