try this, as i said, the code in the other post is a sample, not tested...(my mistake, i should not post such code)
Code:
Sub getvalues()
Dim Path, Myrange, ShName, Wb As String
Path = ThisWorkbook.Path 'enter your source file path
Wb = "Copy of intervale valori.xlsx" 'adjust as needed
Myrange = "A1:b16" 'change as needed
Workbooks.Open Filename:=Path & "\" & Wb
With ThisWorkbook.Sheets("Sheet1") 'change sheet1 to your sheet name
.Activate
Select Case ThisWorkbook.Sheets("Sheet1").Cells(6, "C")
Case 7
ShName = "July-Data"
ActiveSheet.Cells(21, "C") = Application.VLookup(ActiveSheet.Cells(9, "E"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 2, False)
Case 8
ShName = Sheets("Aug-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(ActiveSheet.Cells(9, "E"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 5, False)
Case 9
ShName = Sheets("Sep-Data")
ActiveSheet.Cells(21, "C") = Application.VLookup(ActiveSheet.Cells(9, "E"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 5, False)
Case Else
End Select
End With
Workbooks(Wb).Close , savechanges = True
End Sub
you can change the red string: (
ActiveSheet.Cells(9, "E")-this means that the search string is in cell E9)
Application.VLookup(
ActiveSheet.Cells(9, "E"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 5, False)
with : "Supermarket", or "Super*" (to search for a fragment of text)