Hi all,

I am trying to write a macro that copies data from a spreadsheet each month, however the name of the file will change each month to reflect the latest month. For example the spreadsheet will be titled 'Book1 Example 300613, then the next month the file will be titled Book1 Example 310713. Is there a way I can amend my code to pick up the latest month?

Sub Copy_As_Required()
    Dim cl As Range, r As Range
    Dim ThisWB As Workbook
    Set ThisWB = Workbooks("Book1 Example.xlsm")
    Application.ScreenUpdating = False
    For Each cl In ThisWB.Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)
        cl.Offset(, 1).Copy
        Workbooks("Book2.xlsx").Activate
        On Error Resume Next
        Set r = Range("A:A").Find(What:=cl.Value)
        With r.Offset(, 1)
            .PasteSpecial (-4163)
        End With
        Windows("Book1 Example.xlsm").Activate
    Next
    Application.ScreenUpdating = True
End Sub
Thanks