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