Move all files from one directory to another if they meet criteria

[ddbroadhead92]

Afternoon all,

I have an excel template which emails PDF documents found in a directory to the relevant email addresses. I am trying to add some code into the macro which will also move all the sent files to a new folder called "Sent".

The amount off attachments in each email will vary.

I currently use the below code which only sort of works:

For b = 2 To lastRow

    Dim FSO As Object
    Dim SourceFileName, DestinFileName As String
    Set FSO = CreateObject("Scripting.Filesystemobject")
    Sheets("Email List").Select
    Range("C2").Select
        On Error Resume Next
    SourceFileName = "M:\2017\Collections AR\Group Debts\Small Business\Statements\" & Cells(b, 1) & "*"
    DestinFileName = "M:\2017\Collections AR\Group Debts\Small Business\Statements\Sent\"
    FSO.MoveFile Source:=SourceFileName, Destination:=DestinFileName
    Next

The issue I have is that I may have 20 files that are named something like ACC109 - ????? and I would need it to move every file which has ACC109 -, the code I currently use only moves the first one and then moves on to the next "b" which may be file names like ACC110 - ???? etc.

Hope that makes sense?

I feel like there is a simple solution such as a "do while sourcefilename > 0" or something like that but unfortunately im not quite at that stage in my development yet.

Thanks in advance
Dan

[ddbroadhead92]

Managed to work this out myself, wasn't far off with my initial thoughts. For anyone who encounters something similar, this was my solution:

Sub End_Test()

Application.ScreenUpdating = False

Dim Foldername As String
Foldername = "M:\2017\Collections AR\Group Debts\Small Business\Statements\"


Sheets("Statement").Visible = True
Sheets("Invoice Template").Visible = True
Sheets("ACC.Outstanding").Visible = True

    Sheets("Email List").Select
    Range("C2").Select
    
For b = 2 To 250

    Dim FSO As Object
    Set FSO = CreateObject("Scripting.Filesystemobject")
    Dim SourceFileName, DestinFileName As String
    
    Do
    On Error Resume Next
    SourceFileName = "M:\2017\Collections AR\Group Debts\Small Business\Statements\" & Cells(b, 1) & "*"
    DestinFileName = "M:\2017\Collections AR\Group Debts\Small Business\Statements\Sent\"
    FSO.MoveFile Source:=SourceFileName, Destination:=DestinFileName
        If Err.Number <> 0 Then
    Err.Clear
    GoTo Next1
    End If
    Loop While SourceFileName > 0
Next1:
    Next
    
    Sheets("Statement").Visible = False
    Sheets("Invoice Template").Visible = False
    Sheets("ACC.Outstanding").Visible = False
    
Application.ScreenUpdating = True
  ActiveWorkbook.FollowHyperlink Foldername
MsgBox ("Emails Sent - Print the files remaining in the file"), , "Complete"
    
End Sub