Using partial file names in an excel table to match and copy files to a subfolder

To search for partial file names and copy them to a new subfolder using VBA, you can utilize the `FileSystemObject` from the Microsoft Scripting Runtime library. Here's an example:

```vba
Sub SearchAndCopyFiles()
Dim fso As Object ' FileSystemObject
Dim sourceFolder As String
Dim destinationFolder As String
Dim fileNamePattern As String
Dim file As Object ' File
Dim foundFilesCount As Integer

' Set the source folder path
sourceFolder = "C:\Path\To\Source\Folder"

' Set the destination folder path
destinationFolder = "C:\Path\To\Destination\Folder\Found Files"

' Set the partial file name pattern
fileNamePattern = "*20448*"

' Create a FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")

' Check if the source folder exists
If Not fso.FolderExists(sourceFolder) Then
MsgBox "Source folder does not exist."
Exit Sub
End If

' Check if the destination folder exists and create it if not
If Not fso.FolderExists(destinationFolder) Then
On Error Resume Next
fso.CreateFolder destinationFolder
On Error GoTo 0

' Check if the creation was successful
If Not fso.FolderExists(destinationFolder) Then
MsgBox "Unable to create the destination folder."
Exit Sub
End If
End If

' Loop through all files in the source folder
For Each file In fso.GetFolder(sourceFolder).Files
' Check if the file name matches the pattern
If fso.GetFileName(file.Path) Like fileNamePattern Then
' Copy the file to the destination folder
fso.CopyFile file.Path, destinationFolder & "\" & fso.GetFileName(file.Path)
foundFilesCount = foundFilesCount + 1
End If
Next file

MsgBox "Found files: " & foundFilesCount
End Sub
```

In the code above, you need to set the `sourceFolder` variable to the path of the folder where you want to search for files. Set the `destinationFolder` variable to the path of the folder where you want to copy the found files. The `fileNamePattern` variable represents the partial file name pattern you want to search for (e.g., "*20448*").

The code uses the `FileSystemObject` to check if the source and destination folders exist. If the destination folder doesn't exist, it is created. Then, it loops through all files in the source folder and checks if the file name matches the specified pattern using the `Like` operator. If a match is found, the file is copied to the destination folder using the `CopyFile` method of the `FileSystemObject`.

After running the code, a message box will display the number of found files. The matching files will be copied to the "Found Files" subfolder within the destination folder.