Insert VLOOKUP into cells as a formula

[Smurphy820]

Hello I need some help with inserting VLOOKUP into cells as a formula. The first part of code finds the document that I need to pull data from. My plan was to essentially hard code the formula into all of the cells I need using VBA. Both tabs have the same name, so I thought I could just input the document's name as a variable and put the tab's name after it to complete the middle section of the VLOOKUP formula.

Sub ASM__tabs()


Dim sFound As String
Dim folderpath As String
Dim Sheettitle As String


Name = ActiveWorkbook.Name
namesplit = Split(Name, "-")
Namesplitfinal = namesplit(0)

folderpath = "C:\Users\xxx\OneDrive - xxx\Documents\xxx\Macros\" & Namesplitfinal & "\"

sFound = Dir(folderpath & "Monthly Membership by County*.xlsx")
If sFound <> "" Then
Workbooks.Open Filename:=folderpath & "\" & sFound
Name = ActiveWorkbook.Name
End If

ThisWorkbook.Activate
Worksheets(1).Select
Sheettitle = ActiveSheet.Name

Range("E23:E32").Formula = "=VLOOKUP($B23," & Name & "" & Sheettitle & "!$A$3:$E$261,3,FALSE)"

This is the table I am putting the formula into.

And here is the tab I'm pulling data from.

The problem is the code won't even run, I keep getting this error and it highlights the section of the code with the Vlookup. So I think I am way off and would appreciate some help! thanks!!

End Sub

 

[Anthony47]

Try this way:
-open manually one of the target workbooks ("Monthly Membership by County*.xlsx")
-now insert on the starting workbook the VLOOKUP formula
-examine the ntax of the formula
This will be like =VLOOKUP($B23,'[NameOfTheFile.xlsx]4-22'!$A$3:$E$261,3,FALSE)

Well, your instruction lacks inserting both the square brackets [ ] and both the single-quotation-marks '

Try

Range("E23:E32").Formula = "=VLOOKUP($B23, '[" & Name & "]" & Sheettitle & "'!$A$3:$E$261,3,FALSE)"

 

[Smurphy820]

That worked perfectly! Thank you so much!

 

[Anthony47]

Thank you for the feedback