Macro to open multiple files with changing names

Hello,
I am trying to make a macro to open a series of .csv files and copy data into the active workbook. The names of the files are different each time, with a common suffix.
An example file name would be "SPW2292-11-(A-B-C)-G_I13_C1_002-w-vFine-X-Top" where "SPW2292-11-(A-B-C)-G_I13_" will change each time.

Is there a way to open files with just the last section of the name?

Here is what I have now that will open the first file:

    Filename = ThisWorkbook.Name
    RunNo = Mid(Filename, 17, 6)
    FilePath = ActiveWorkbook.FullName
    FolderName = Mid(FilePath, 1, 29)
    'MsgBox "RunNo is " & RunNo
    'MsgBox "FilePath is " & FilePath
    'MsgBox "FolderName is " & FolderName
        
    Wafer = "W" + RunNo
    WaferNo = "A-G"
    SheetNo = "W" + Mid(RunNo, 1, 4) + "-" + Mid(RunNo, 5, 6) & WaferNo    
    
    If Dir("C:\X'Pert Data\Wafers\" & Wafer & "\" & SheetNo & "\" & "SPW2292-11-(A-B-C)-G_I13_C1_002-w-vFine-X-Top.csv") <> "" Then
        SourceFile = "C:\X'Pert Data\Wafers\" & Wafer & "\" & SheetNo & "\" & "SPW2292-11-(A-B-C)-G_I13_C1_002-w-vFine-X-Top.csv"
        Workbooks.Open Filename:=SourceFile
        Range("A34:B34").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Workbooks(Filename).Activate
        Sheets(WaferNo).Select
        Range("A2").Select
        ActiveSheet.Paste
        Workbooks.Open Filename:=SourceFile
        ActiveWorkbook.Close True
    Else:
        MsgBox "File not found."
    End If

Try something like this (not tested).

    Dim FileName As String, RunNo As String, FilePath As String, FolderName As String
    Dim SourceFile As String, Wafer As String, WaferNo As String, SheetNo As String
    Dim wbSource As Workbook
    
    FileName = ThisWorkbook.Name
    RunNo = Mid(FileName, 17, 6)
    FilePath = ActiveWorkbook.FullName
    FolderName = Mid(FilePath, 1, 29)
    'MsgBox "RunNo is " & RunNo
    'MsgBox "FilePath is " & FilePath
    'MsgBox "FolderName is " & FolderName
        
    Wafer = "W" + RunNo
    WaferNo = "A-G"
    SheetNo = "W" + Mid(RunNo, 1, 4) + "-" + Mid(RunNo, 5, 6) & WaferNo
    
    SourceFile = Dir("C:\X'Pert Data\Wafers\" & Wafer & "\" & SheetNo & "\*_C1_002-w-vFine-X-Top.csv")
    If SourceFile <> "" Then
        Do
            Set wbSource = Workbooks.Open(FileName:=SourceFile)
            Range(Range("A34:B34"), Range("A34:B34").End(xlDown)).Copy _
                Destination:=ThisWorkbook.Sheets(WaferNo).Range("A" & Rows.Count).End(xlUp).Offset(1)
                'Paste to next empty row
            wbSource.Close False
            SourceFile = Dir    'Next file
        Loop While SourceFile <> ""
    Else:
        MsgBox "File not found. ", vbInformation, "No File Match Found"
    End If

Thanks, that seems much easier to follow, but I am still getting an error (Run-time error 1004) that says the file cannot be found.
The files are in separate folders than the excel file that the macro is in, so it looks like I need to add a way for the wbSource to find the SourceFile in the correct folder?

How would I add that in the Loop?

Sorry I missed that. Try this...

    Dim FileName As String, RunNo As String, FilePath As String, FolderName As String
    Dim SourceFile As String, Wafer As String, WaferNo As String, SheetNo As String
    Dim wbSource As Workbook, strPath As String
    
    FileName = ThisWorkbook.Name
    RunNo = Mid(FileName, 17, 6)
    FilePath = ActiveWorkbook.FullName
    FolderName = Mid(FilePath, 1, 29)
    'MsgBox "RunNo is " & RunNo
    'MsgBox "FilePath is " & FilePath
    'MsgBox "FolderName is " & FolderName
        
    Wafer = "W" + RunNo
    WaferNo = "A-G"
    SheetNo = "W" + Mid(RunNo, 1, 4) + "-" + Mid(RunNo, 5, 6) & WaferNo
    strPath = "C:\X'Pert Data\Wafers\" & Wafer & "\" & SheetNo & "\"
    
    SourceFile = Dir(strPath & "*_C1_002-w-vFine-X-Top.csv")
    If SourceFile <> "" Then
        Do
            Set wbSource = Workbooks.Open(FileName:=strPath & SourceFile)
            Range(Range("A34:B34"), Range("A34:B34").End(xlDown)).Copy _
                Destination:=ThisWorkbook.Sheets(WaferNo).Range("A" & Rows.Count).End(xlUp).Offset(1)
                'Paste to next empty row
            wbSource.Close False
            SourceFile = Dir    'Next file
        Loop While SourceFile <> ""
    Else:
        MsgBox "File not found. ", vbInformation, "No File Match Found"
    End If

Originally Posted by AlphaFrog

Sorry I missed that. Try this...

Don't worry, I accidentally left that out.
Thanks a lot for the help!