I would suggest something similar to the while loop suggested by Alex, but different and expanding a little on your actual requirements, and it could be argued that it is not a loop in a traditional for/while sense. For one, to avoid a while loop going on forever some counter and limit should be applied, in case no file exist. To also make it reusable if you want to automate a traverse through a range of various Copy and Paste paths i took out the 'loop' part and placed it in a seperate function:
Sub main()
Dim vSuccess As Boolean
Dim iterLimit As Integer ' iteration Limit, as in max number of times we want to go through
Dim i As Integer
Dim vDate As Date
Dim copyFolder As String
Dim pasteFolder As String
iterLimit = 30 'for easier future followup, could be given directly into the function call
i = 1 'same as above
vDate = Date 'same as above
copyFolder = "ABC\"
pasteFolder = "XYZ\"
vSuccess = IfDoesExist(copyFolder, pasteFolder, vDate, i, iterLimit) 'put it into the function IfDoesExist
If vSuccess Then 'if the funciton returns True a match was found and the file copied
MsgBox "Success, the file was copied"
Else 'if not then do something
MsgBox "No file found"
End If
End Sub
Function IfDoesExist(copyFolder As String, pasteFolder As String, vDate As Date, i As Integer, iterLimit As Integer)
Dim Yday As Date
Dim YdayYear As Integer
Dim YdayMonth As Integer
Dim YdayDay As Integer
Dim CopyPath As String
Dim PastePath As String
Yday = DateAdd("d", -i, vDate)
YdayYear = DatePart("yyyy", Yday)
YdayMonth = DatePart("m", Yday)
YdayDay = DatePart("d", Yday)
CopyPath = copyFolder & YdayYear & YdayMonth & YdayDay & ".csv"
PastePath = pasteFolder & YdayYear & YdayMonth & YdayDay & ".csv"
If iterLimit > 0 Then
If Dir(CopyPath) <> "" Then
FileCopy Source:=CopyPath, Destination:=PastePath
vStatus = True 'we have a match
Else 'if the file doesn't exist we want to rerun all of the above with a new i and iterLimit
iterLimit = iterLimit - 1
i = i + 1
'Ok i know the next line of code may seem odd, but you will get True or False.
'When the function stops calling itself the vStatus is either true because a
'match was eventually found, or false if it ws not. The value then travels back
'through the calls/stack and ends up in the main programme.
'
'put in a breakpoint an take a look at the locals if you want to see the magic happen
vStatus = IfDoesExist(copyFolder, pasteFolder, Date, i, iterLimit)
End If
Else
vStatus = False 'if a match was never found within iterLimit calls
End If
IfDoesExist = vStatus 'return vStatus
End Function
This is perhaps to overcomplicate things, but I had fun doing it. Though there is no while or for loop the function will effectivly work the same by calling itself. To avoid an infinite number of iterations, the iterLimit is decremented by 1 for each call.
The template to go over a range of paths, is not applied in the code but if you look at Loop through each cell in a range of cells when given a Range object you may get an idea of how it could be done
modified for paths it worked on my system, but if you are up for trying and it fails on your system, please let me know which error you get
EDIT:
to answer your question in short, a for loop should do it:
Sub main2()
Dim i As Integer
Dim Yday As Date
Dim YdayYear As Integer
Dim YdayMonth As Integer
Dim YdayDay As Integer
Dim CopyPath As String
Dim PastePath As String
Dim vMax As Integer
Dim vStatus As Boolean
vMax = 30
For i = 1 To vMax
Yday = DateAdd("d", -i, Date)
YdayYear = DatePart("yyyy", Yday)
YdayMonth = DatePart("m", Yday)
YdayDay = DatePart("d", Yday)
CopyPath = "ABC\" & YdayYear & YdayMonth & YdayDay & ".csv"
PastePath = "XYZ\" & YdayYear & YdayMonth & YdayDay & ".csv"
If FileExists(CopyPath) Then
FileCopy Source:=CopyPath, Destination:=PastePath
vStatus = True
Exit For
Else
i = i + 1
End If
Next
If Not vStatus = True Then
MsgBox "File Not found"
End If
End Sub
