How to move multiple files when there is a different number each time

Question

I have to pull a varying number reports each day that all have similar names (ReportWizard_ with date and time in the file name). Once I pull the reports, I save them based on text in cell C5 of each report. Some days there are as few as 5 reports and other days as many as 30. I have a macro that opens one of the reports and saves is based on a value in cell C5. I have been just running the macro manually until all reports are moved and renamed. How can I loop through all of them as long as there is a file named "ReportWizard_*" in the folder? My current code is below.

'''

Sub ReportWizardSaveMaster()
 Call ReNameReportWizard
 Call OpenWorkingFile1
 Call ReportSaver
 Kill "C:\Downloads\Report Wizard Working Folder\*.*"
 End Sub

 Sub ReNameReportWizard()
      Dim ReportWizardfile As String
      Dim OldPath As String
      Dim NewPath As String
           OldPath = "C:\Downloads\"
           NewPath = "C:\Downloads\Report Wizard Working Folder\"
                If Len(Dir(NewPath, vbDirectory)) = 0 Then MkDir (NewPath)
           ReportWizardfile = Dir("C:\Downloads\ReportWizard_*")
           NewName = "1.xls"
           Name OldPath & ReportWizardfile As NewPath & NewName
 End Sub

 Sub OpenWorkingFile1()
      Dim sFound As String
      Dim Path As String
      Path = "C:\Downloads\Report Wizard Working Folder\"
      sFound = Dir(Path & "\1.xls")
           If sFound <> "" Then
                Workbooks.Open Filename:=Path & "\" & sFound
           End If
 End Sub

 Sub ReportSaver()
      Dim Path As String
      Dim FName As String
      Path = "C:\Reports\"
      FName = Sheets(1).Range(C5).Text
           If Len(Dir(Path, vbDirectory)) = 0 Then MkDir (Path)
           If Len(Dir(Path & FName, vbDirectory)) = 0 Then MkDir (Path & FName)

 ActiveWorkbook.SaveAs Filename:=Path & FName & "\" & FName & " " & Format(Now(), "YYYY MM DD") & ".xlsx", FileFormat:=51
      End Sub
 

Answer 1

I think this is easier to manage as a single sub:

EDIT: fix nested use of Dir()

Sub ReportWizardSaveMaster()
    Const SRC_PATH As String = "C:\Downloads\"
    Const FILE_PATTERN As String = "ReportWizard_*"
    Const REPORT_PATH As String = "C:\Reports\"
    
    Dim files As Collection, f, wb As Workbook, FName As String, reportFolder As String
    
    If Len(Dir(REPORT_PATH, vbDirectory)) = 0 Then MkDir REPORT_PATH
    
    Set files = FileMatches(SRC_PATH, FILE_PATTERN) 'first find all matching files
    Debug.Print "Found " & files.Count & " files to process"
    For Each f In files    'loop over the returned files
        Set wb = Workbooks.Open(SRC_PATH & f, ReadOnly:=True)
        FName = wb.Worksheets(1).Range("C5").Value
        reportFolder = REPORT_PATH & FName & "\"
        If Len(Dir(reportFolder, vbDirectory)) = 0 Then MkDir reportFolder
        wb.SaveAs _
           Filename:=reportFolder & FName & " " & Format(Now(), "YYYY MM DD") & ".xlsx", _
           FileFormat:=51
        wb.Close False
    Next f
    Kill SRC_PATH & FILE_PATTERN
End Sub

'Look in folder `folderPath` for all files matching `filePattern` and
'  return all filenames as a Collection
Function FileMatches(ByVal folderPath As String, filePattern As String) As Collection
    Dim f
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    Set FileMatches = New Collection
    f = Dir(folderPath & filePattern)
    Do While Len(f) > 0
        FileMatches.Add f 'add the file
        f = Dir()         'next file
    Loop
End Function