Sub getvalue()
Dim Path, Myrange, ShName, Wb As String
Path = "\\ABC"
Wb = "Report-1c-Server.xlsx"
Myrange = "A9:C9700" 
Workbooks.Open Filename:=Path & "\" & Wb
 With ThisWorkbook.Sheets("EXHIBIT 6-A") 
 .Activate
Select Case ThisWorkbook.Sheets("EXHIBIT 6-A").Cells(6, "G")
Case 7
I want Create a new worksheet here 
ShName = "Aramco-MP-July"
ActiveSheet.Cells(9, "A") = Application.VLookup(ActiveSheet.Cells(5, "J"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 3, False)
Case 8
ShName = Sheets("Aramco-MP-Aug")
ActiveSheet.Cells(21, "A") = Application.VLookup(ActiveSheet.Cells(9, "J"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 5, False)
I want delete a new worksheet here 
Case Else
End Select
End With
Workbooks(Wb).Close , savechanges = True
End Sub