How To Search & Find Specific a Specific Text String Within a Sheet Name

[MercuryVBA]

Hello,

Can someone please help me with the code/syntax used to search and find a specific text string withing sheet names.

For Example,

I have a sheet titled "Current Report - 09-08-2014"

I need to do 2 things:

1. I need help with vba code that can check if the sheet name contains "Current Report".

2. I need help with vba code to pick up the date from the sheet name and store it in a variable.

Assistance would be greatly appreciated. Thank you!

 

[Momentman]

I am not sure if you would have more than one sheet with the text "Current Report". So what I have done is to put a YES in cell AA! of the sheet if it contains the required text and the date is placed in cell(AA2)

Sub checksheetname()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(1, ws.Name, "Current Report") Then
            ws.Range("AA1").Value = "YES"
            ws.Range("AA2").Value = Right(ws.Name, 10)
        End If
    Next ws
End Sub

 

[MercuryVBA]

I am not sure if you would have more than one sheet with the text "Current Report". So what I have done is to put a YES in cell AA! of the sheet if it contains the required text and the date is placed in cell(AA2)

Sub checksheetname()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(1, ws.Name, "Current Report") Then
            ws.Range("AA1").Value = "YES"
            ws.Range("AA2").Value = Right(ws.Name, 10)
        End If
    Next ws
End Sub

Hello, Thank you for your response.

Yes, I may have multiple tabs with "Current Report" however, I was looking for a solution that does not require using cells from the spreadsheet.
Are you aware of such an avenue? Thank you!

 

[Momentman]

Well, you could use an array to store the dates

Sub checksheetname()
    Dim ws As Worksheet
    Dim arr()
    Dim I As Integer
    I = 0
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(1, ws.Name, "Current Report") Then
            ReDim Preserve arr(I)
            arr(I) = Right(ws.Name, 10)
            I = I + 1
        End If
    Next ws
End Sub

Arr(0) will give you the first date, Arr(1) will give you the 2nd date and so on.....

 

[MercuryVBA]

Well, you could use an array to store the dates

Sub checksheetname()
    Dim ws As Worksheet
    Dim arr()
    Dim I As Integer
    I = 0
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(1, ws.Name, "Current Report") Then
            ReDim Preserve arr(I)
            arr(I) = Right(ws.Name, 10)
            I = I + 1
        End If
    Next ws
End Sub

Arr(0) will give you the first date, Arr(1) will give you the 2nd date and so on.....

Perfect. That gave me just what I was looking for. Thank you sincerely for your time and help on this post!

 

[Momentman]

You are welcome