How to handle sheet name case sensitivity

[Rudi]

Hi,

Stepping through a current macro I'm working on I noticed that the sheet names (in an IF block) is case sensitive. What would be the best way to get around this if the workbook comes from various sources and the sheet names can be in various cases like DERIVATIVES or Derivatives or derivatives or even dERIVATIVES (not that this one is likely!)

I have modified it as below, but is it necessary to add all the other variations using OR's, or is there a easier/shorter way or testing this?

TX

Code: Select all

        If sh.Name = "DERIVATIVES" Or sh.Name = "BROKERAGE" Or _
            sh.Name = "Derivatives" Or sh.Name = "Brokerage" Then
            Range(rStart, rEnd).EntireRow.Copy
            Workbooks(sNewWBName).Activate
            Worksheets(sh.Name).Activate
            Range("A4").PasteSpecial xlPasteColumnWidths
            Range("A4").PasteSpecial
            Range("A4").Select
        Else

[HansV]

Option 1: use UCase:

Code: Select all

        Select Case UCase(sh.Name)
            Case "DERIVATIVES", "BROKERAGE"
                Range(rStart, rEnd).EntireRow.Copy
                ...
            Case Else
                ...
        End Select

Option 2:

Insert the following line at the top of the module, immediately above or below the line Option Explicit:

Code: Select all

Option Compare Text

This means that all text comparisons within the module will be case-insensitive: A=a, É=é, etc. You can then use

Code: Select all

        Select Case sh.Name
            Case "Derivatives", "Brokerage"
                Range(rStart, rEnd).EntireRow.Copy
                ...
            Case Else
                ...
        End Select

[Rudi]

Option 2 is quicker and I know that case is not an issue in this file.

TX.

Any reason why you changed to Select Case?

[HansV]

I find Select Case easier to read than a series of repeated conditions. It's also easier to add more values: compare

Code: Select all

    If sh.Name = "This" Or sh.Name = "That" Or sh.Name = "Such" Or _
        sh.Name = "What" Or sh.Name = "Other" Or sh.Name = "Home" Then

to

Code: Select all

    Select Case sh.Name
        Case "This", "That", "Such", "What", "Other", "Home"

[Rudi]

Understood.
TX again (and for the tip).