When running VBA Excel Macro with variant filename hardcoded value and stopped working

Question

Greetings dear experts, I created a macro to open a variable filename and copy paste values to a specific excel file. It worked previously but when I changed file it stopped working because debug shows it is looking for previous file name. This makes no sense hence I dim a variable name with * so it will always look for a file that has starting name as 2021*

Here's the code

Sub BuildingCSV1()
'
' BuildingCSV1 Macro
'

'
Range("A1").Select
Columns("A:C").Select
Selection.ClearContents
Range("A1").Select
Dim fname As Variant
Dim myPath As String

myPath = "C:\Users\FIN-BC\Documents\Automation Anywhere Files\Automation Anywhere\My Docs\POReceipt\"
fname = Dir("2021*.CSV")
Workbooks.Open (myPath & fname)
Application.Workbooks(1).Activate
Application.Workbooks(2).Activate
Columns("E:E").Select
Selection.Copy
Application.Workbooks(1).Activate
ActiveSheet.Paste
Application.Workbooks(2).Activate
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Application.Workbooks(1).Activate
Range("B1").Select
ActiveSheet.Paste
Application.Workbooks(2).Activate
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Application.Workbooks(1).Activate
Columns("C:C").Select
ActiveSheet.Paste
Range("A1").Select
End Sub

The sentence "fname = Dir("2021*.CSV")" is the one supposed to be able to open a different csv file when the file name has "2021" at the start. In this case debug shows its looking for previous file "20210427.csv" when it is supposed to open new file "20210504.csv" and so on for the further files
Hope you can help me, I couldn't find a way to make it works
Regards

Answer 1

If your filenames always consist of year, month and day, then try replacing ‘fname = Dir("2021*.CSV")’ with this fragment:

Dim n As String
n = Dir("2021*.CSV")
fname = n
Do While n <> ""
    n = Dir
    If fname < n Then fname = n
Loop