Sheet names that change with date

[Hedgingthehedge]

I have the below code which is quite efficient for grabbing a sheet from a closed work book and then pasting it where it needs to go. How ever I cant find consistency when the date changes in the sheet name itself...

Sub UpdateRecord()
Dim SourceWB As String, SourceFile As String, SourceWS As Worksheet, DestWS As Worksheet
SourceWB = "DwightPDTrials.xlsm"
'Configure Destination Data Location
Set DestWS = Workbooks("TestD.xlsm").Sheets("Trial")

'Configure Source Data Location
SourceFile = "A:\Group\ECDI\Dwight\TRIAL IMPORT\" & SourceWB
Workbooks.Open Filename:=SourceFile
Set SourceWS = ActiveWorkbook.Sheets("T QAHZ A 22MAY15") '--> The date changes everyday which is my problem. The end goal is to save over a constant locaiton so the file is good, its just the sheets. I need the identifyer to only need "T QAHZ A" to locate the right sheet but when I try to only do that, I get a subscript out of range error. What is the best way to set SourceWS based only off the part of the sheet name that will always remain constant?
SourceWS.Activate

'Inspect Source Data Record
Cells.Select
Selection.Copy
DestWS.Activate
Cells.Select
ActiveSheet.Paste
'Close Source Workbook
Workbooks(SourceWB).Close SaveChanges:=False
End Sub

Thanks,
HTH

 

[V_Malkoti]

Set oWB = Workbooks.Open(Filename:=SourceFile)
For Each oWS In oWB.Sheets
    If oWS.Name Like "T QAHZ A *" Then
        Set SourceWS = oWS
        Exit For
    End If
Next oWS

Debug.Print SourceWS.Name

 

[Hedgingthehedge]

Thank you good sir!

 

[V_Malkoti]

You are welcome!