Obviously, "nothing happens" using my code in #3 because no base filenames, less the file extension, matched a sheet's name.

When seeking help and your data is sensitive, obfuscate it and attach that. Or, just make simple files with a small dataset that shows your format.

From your first post, those are not csv files since they are double quote delimited and semi-colon separated as defined in your Workbooks.OpenText options in #1. CSV files are comma separated value file type. If they were true CSV file types, other methods besides OpenText could be used.

You can use Filter() like this below to get the partial matches. If you rename the file extensions to txt, then this macro would import the data as one expects. It still does but it is all in one column for the psuedo csv files if you rename the txt files that I posted to be csv.

As usual, test on backup copy of master file.

Sub Main()  
  Dim p As String, fe As String, fileToOpen As String, i As Integer, j As Integer
  Dim twb As Workbook, ws As Worksheet, ws1 As Worksheet, r As Range
  Dim a, b, f
  
  'p = "C:\User\testfiles\"
  p = ThisWorkbook.Path & "\"
  fe = ".csv"
  
  'Put all base filenames with file extension fe in folder p into array a.
  a = Split(CreateObject("Wscript.Shell").Exec("cmd /c dir " & """" & p & "*" & fe & """" & " /b").StdOut.ReadAll, vbCrLf)
  If UBound(a) < 0 Then Exit Sub
    
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.DisplayAlerts = False
  
  Set twb = ThisWorkbook
  
  For i = 2 To twb.Worksheets.Count 'WorkSheet(1)=Welcome sheet, so skip.
    Set ws = twb.Worksheets(i)
    b = Filter(a, "_" & ws.Name & "_", True, vbTextCompare)
    If UBound(b) < 0 Then GoTo Nexti
    For j = 0 To UBound(b)
      Workbooks.OpenText _
        Filename:=b(j), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=True, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), _
        TrailingMinusNumbers:=True
      
      ActiveSheet.UsedRange.Copy
      Set r = ws.Cells(Rows.Count, "C").End(xlUp).Offset(1)
      If r.Row < 4 Then Set r = ws.Range("C4")
      r.PasteSpecial xlPasteValues
      
      ActiveWorkbook.Close False
    Next j
Nexti:
  Next i
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Application.DisplayAlerts = True
End Sub