CODEWITHSUNDEEP
excelvba
Nima
Nima

Reputation: 1

Select worksheet with name with specific characters

I have a workbook with several sheets. In this case I cannot use Index instead of Select because the location of the sheet I am selecting may change in reference to the other sheets. Here is an example

Sheets in my workbook: Final Report, Alpha_A, Beta

I need to populate the B column of "Final Report" with the last character in the "Alpha_A" sheet (in this case, "A"). In that case I am currently using

 Sheets("Alpha_A").Select
 Range("B3:B5000").Value = Right(ActiveSheet.Name, 1)

However, this is done in several different workbooks, where each one has Final Report and Beta sheets, but the Alpha sheet may have a different character at the end of the name (ex. Alpha_A, Alpha_B, Alpha_C, etc...).

I am looking for a code to select the sheet in the workbook that contains the word "Alpha" so that I do not have the change the code when the last character of the Alpha sheet changes. Thanks for the help!

Upvotes: 0

Views: 1541

Answers (2)

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

base on the Gary's Student code, according to what I understand, may be~: 

Sub test()
    Dim s As Worksheet
    For Each s In ThisWorkbook.Sheets
        If InStr(s.Name, "Alpha_") Then
            s.Range("B3:B5000").Value = Right(s.Name, 1):exit sub
        End If
    Next s
End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

If there is only 1 letter following the underscore, then:

Sub luxation()
    Dim s As Worksheet
    For Each s In ThisWorkbook.Sheets
        If Left(Right(s.Name, 7), Len(s.Name) - 1) = "Alpha_" Then
            Sheets("Final Report").Range("B3:B5000").Value = Right(s.Name, 1)
        End If
    Next s
End Sub

(Note: Select was not required)
(If there is possible more than 1 letter following the underscore, a slight mod is required)

Upvotes: 1

Related Questions