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