VBA Code to Move Excel files from one folder to another

Are these account numbers listed on a sheet to manage this process from?

What is sheet name?
What column has these account numbers?
Is there any other data in that same column anywhere to cause an issue?
Do we want to "mark" the account numbers that were successfully found and moved, or mark the ones that weren't found, in some way?

Would the macro be run FROM inside the Waive2PIAccounts.xls workbook, or from a separate workbook? In that workbook, the first account number is listed in A1? Or A2? ??

In this example folder:

K:\shared\InvestorBankRec\Reconciliations\1234567890\

you note these two files to be moved:
1234567890_Top Sheet_2013.xls
1234567890_Rec Worksheet_2013.xls

Are there other files in that folder to be ignored, or are these two always the only files in there? I ask because if this represents all the contents, we could just move the folder as a whole rather than file by file.

This seems like it should work, then. It will note off to the right any files it cannot find.

Option Explicit

Sub MoveFiles()
Dim wbAccounts As Workbook, Accounts As Range, Account As Range, ACC As String
Dim fromPATH As String, toPATH As String, pdfPATH As String, fNAME As String, wasOPEN As Boolean

fromPATH = "K:\shared\InvestorBankRec\Reconciliations\"
pdfPATH = "K:\shared\InvestorBankRec\Electronic Bank Statements\2013\07-2013\"
toPATH = "K:\shared\InvestorBankRec\Audits\W2 07-2013\"

On Error Resume Next
Set wbAccounts = Workbooks("Waive2PIAccounts.xls")
If wbAccounts Is Nothing Then
    Set wbAccounts = Workbooks.Open("C:\Documents and Settings\mhamid\Desktop\Wave 2 Accts\Waive2PIAccounts.xls")
Else
    wasOPEN = True
End If

Set Accounts = wbAccounts.Sheets(1).Range("A2:A" & Rows.Count).SpecialCells(xlConstants)

For Each Account In Accounts
    ACC = Account.Text
    
    fNAME = Dir(fromPATH & ACC & "\" & ACC & "_Top Sheet_2013.xls")
    If Len(fNAME) > 0 Then
        Name fromPATH & ACC & "\" & fNAME As toPATH & fNAME
        fNAME = ""
    Else
        Account.Offset(, 3).Value = "Top Sheet Not Found"
    End If
    
    fNAME = Dir(fromPATH & ACC & "\" & ACC & "_Rec Worksheet_2013.xls")
    If Len(fNAME) > 0 Then
        Name fromPATH & ACC & "\" & fNAME As toPATH & fNAME
        fNAME = ""
    Else
        Account.Offset(, 4).Value = "Rec Sheet Not Found"
    End If

    fNAME = Dir(pdfPATH & "*111-" & ACC & "-2013*.pdf")
    If Len(fNAME) > 0 Then
        Name pdfPATH & fNAME As toPATH & fNAME
        fNAME = ""
    Else
        Account.Offset(, 5).Value = "PDF Not Found"
    End If
Next Account

If Not wasOPEN Then wbAccounts.Close True

End Sub

Try this instead:

Option Explicit

Sub MoveFiles()
Dim wbAccounts As Workbook, Accounts As Range, Account As Range, ACC As String
Dim fromPATH As String, toPATH As String, pdfPATH As String, fNAME As String, wasOPEN As Boolean

fromPATH = "K:\shared\InvestorBankRec\Reconciliations\"
pdfPATH = "K:\shared\InvestorBankRec\Electronic Bank Statements\2013\07-2013\"
toPATH = "K:\shared\InvestorBankRec\Audits\W2 07-2013\"

On Error Resume Next
Set wbAccounts = Workbooks("Waive2PIAccounts.xls")
If wbAccounts Is Nothing Then
    Set wbAccounts = Workbooks.Open("C:\Documents and Settings\mhamid\Desktop\Wave 2 Accts\Waive2PIAccounts.xls")
Else
    wasOPEN = True
End If

Set Accounts = wbAccounts.Sheets(1).Range("A2:A" & Rows.Count).SpecialCells(xlConstants)

For Each Account In Accounts
    ACC = Account.Text
    
    fNAME = Dir(fromPATH & ACC & "\" & ACC & "_Top Sheet_2013.xls")
    If Len(fNAME) > 0 Then
        FileCopy fromPATH & ACC & "\" & fNAME, toPATH & fNAME
        fNAME = ""
    Else
        Account.Offset(, 3).Value = "Top Sheet Not Found"
    End If
    
    fNAME = Dir(fromPATH & ACC & "\" & ACC & "_Rec Worksheet_2013.xls")
    If Len(fNAME) > 0 Then
        FileCopy fromPATH & ACC & "\" & fNAME, toPATH & fNAME
        fNAME = ""
    Else
        Account.Offset(, 4).Value = "Rec Sheet Not Found"
    End If

    fNAME = Dir(pdfPATH & "*111-" & ACC & "-2013*.pdf")
    If Len(fNAME) > 0 Then
        FileCopy pdfPATH & fNAME, toPATH & fNAME
        fNAME = ""
    Else
        Account.Offset(, 5).Value = "PDF Not Found"
    End If
Next Account

If Not wasOPEN Then wbAccounts.Close True

End Sub

Copying files, not moving them? Hm, will have to look at it later. Time for work.

Nothing prevents duplication. If the list of account numbers includes duplicated account numbers, the files will be copied twice. But that's harmless, you can only have one file named one thing in one folder, so the new copies simply overwrite the existing.