Open file based on its filename date and copy its content

[tkc123]

Hi everyone,

I've been trying to create an Active X button to open a file based on the latest date on the filename and copy (the content) from its worksheet to my current workbook.

Inspiration drew from:
https://www.mrexcel.com/forum/excel...el-file-based-latest-date-found-filename.html

But Excel keeps crashing on every other run.
Could someone please check if the code is properly written?

Thank you so much in advance.

Private Sub CommandButton1_Click()    
    Dim dtTestDate As Date
    Dim sStartWB As String
    Dim x As Workbook
    
    
    Const sPath As String = "C:\Folder\"
    Const dtEarliest = #1/1/2018# 

    dtTestDate = Date
    sStartWB = ActiveWorkbook.Name
    
    While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Set x = Workbooks.Open(sPath & "Record - " & Format(dtTestDate, "YYYY-MM") & ".xlsx")
        dtTestDate = dtTestDate - 1
        On Error GoTo 0


    Wend




    If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."


        x.Sheets("Layout").Range("A1:B100").Copy
        ThisWorkbook.Sheets("Sheet1").Range("A1:B100").PasteSpecial
        
    'Close x:
    x.Close
End Sub

 

[Grand]

Try to put a breakpoint in the begining of your code and run it to see where exactly is the problem.

 

[tkc123]

Apparently, the problem comes from clicking the command button.
I didn't encounter any issues by running the code directly in Visual Basic Editor, but it crashes when I click on the command button.

And how to prevent the macro from opening earlier files if the file with the latest date is already opened?