Sub getvalue()
Dim Path, myrange, ShName, Wb As String
Path = "\\Server Folder"
Wb = "Report-1c-Server.xlsx"
myrange = "A9:C9700" 
Workbooks.Open Filename:=Path & "\" & Wb
 With ThisWorkbook.Sheets("EXHIBIT 6-A") 'change sheet1 to your sheet name
 .Activate
       
Select Case ThisWorkbook.Sheets("EXHIBIT 6-A").Cells(5, "S")
Case 7
ShName = "Arxxx-MP-July"
ActiveSheet.Cells(7, "B") = Application.VLookup(ActiveSheet.Cells(4, "G"),
Workbooks(Wb).Sheets(ShName).Range(myrange), 3, False)
Case Else
End Select
End With
Workbooks(Wb).Close , savechanges = True
End Select
End Sub