Try this

Private Sub FindRegionName()
    
    Dim RCode As Range, Table As Range, RName As String
    
      Workbooks.Open Filename:="J:\Planning & Resources\Portfolio Office\Finance\Budget 2012\Management reports 2012\International\Regional Report Names.xls"

        Set RCode = Range("a2")

           Set Table = Cells(1, 1).Resize(Rows.Count, Columns.Count)
                RName = Application.WorksheetFunction.VLookup(RCode, Table, 2, False)
           MsgBox RName

    ActiveWorkbook.Close

End Sub
Edit: protonLeah beat me to it!