Wildcard macro to move file

Good Afternoon

I am looking for a macro that can move a file I have that has a different name every day to another location.

The file name for example is:

test20130119

the next day

test20130120 etc

I want to be able to use a wildcard search to search the location drive "z\" for a file that has the word test and then the rest that changes when it finds something it will then move the file elsewhere.

Does it have to search all of drive Z including sub-folders or do you know the specific folder to search e.g. "Z:\MyFolder\" ?

This will move all "Test" files from one specific folder to another specific folder. It doesn't search subfolders. Change the Source and Destination paths to suit.

Sub Move_Files()
    
    Dim strFile As String, counter As Long
    
    Const strSourcePath As String = "C:\Temp\"
    Const strDestinationPath As String = "C:\Temp2\"
    
    strFile = Dir$(strSourcePath & "Test*")
    
    Do While strFile <> ""
        Name (strSourcePath & strFile) As (strDestinationPath & strFile)
        counter = counter + 1
        strFile = Dir$()
    Loop
        
    MsgBox counter & " files moved. ", vbInformation, "Files Moved"
    
End Sub

Hi AlphaFrog

Perhaps adding some error trapping to your code if file exists in destination folder?

 Do While strFile <> ""
        On Error Resume Next
        Name (strSourcePath & strFile) As (strDestinationPath & strFile)
        If Err.Number = 58 Then
        Else
        counter = counter + 1
        End If
        strFile = Dir$()
        
    Loop

Alf

Thanks again for your response

I have been playing around trying to get this to work myself, A couple of things I have noticed with your code when I ran it is that:

I have Test in cell A1 and then a date in cell A2 but it only seems to reference cell A1, when it moves the file it calls it Test Test and does not move it in an excel format

what I want is the file to be called Test (or what ever is in cell A1 and then a date that is in A2.

Thanks so much for your help it is much appreciated.

Thats great thanks for all your help on this.