VBA - Referencing file with changing date

One way, maybe:

Dim lDate As Long
Dim sDate As String

lDate = Application.Evaluate("=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)")
sDate = Application.WorksheetFunction.Text(lDate, "dd/mm/yyyy")

Regards, TMS

The EOMONTH() formula will tell you the last day of any specific month.

This will tell you the last day of this month:
=EMONTH(TODAY(), 0)

This will tell you the last day of last month:
=EMONTH(TODAY(), -1)

...etc.

With that you have all you need to "look" for the file based on end of month dates, say for the last 3 months.

Sub Copy_As_Required()
Dim cl As Range, r As Range
Dim SrcWB As Workbook, fNAME As String, i As Long, DestWS As Worksheet

Set DestWS = ThisWorkbook.Sheets("Sheet1")          'edit to the name of the sheet we are copying values INTO

    For i = 0 To 3                                  'check the last 3 months for the EOMONTH file
        fNAME = "C:\2013\Book1 Example " & Format(Evaluate("EOMONTH(TODAY(), " & -i & ")"), "DDMMYYYY") & ".xlsx"
        If Len(Dir(fNAME)) > 0 Then                 'if the file exists, open it
            Application.ScreenUpdating = False
            Set SrcWB = Workbooks.Open(fNAME)
            Exit For
        End If
    Next i

    If SrcWB Is Nothing Then                         'if no file was found
        MsgBox "End of Month file not found for the past 3 months."
        Exit Sub
    End If

    On Error Resume Next                            'update each value from found file into DestWS
    For Each cl In SrcWB.Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row)
        Set r = DestWS.Range("A:A").Find(cl.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not r Is Nothing Then
            r.Offset(, 1).Value = cl.Offset(, 1).Value
        End If
    Next cl

    SrcWB.Close False                               'close the opened file
    Application.ScreenUpdating = True
End Sub