Vlookup multiple worksheets between two workbook

[yoitsmejy]

Hi,

I am trying to write a vlookup macro that will look up values from a different workbook. Both workbooks have the same Tab name. I would like the worksheets to correspond to the name of the worksheet. Below is what i have so far and when i run the macro, it is asking to select sheet. It would be nice if i dont have to select the sheets everytime it runs. Any suggestion is welcome and appreciated.

[VBA]Sub Macro5()
Dim ws As Worksheet
Dim sString As String

sString = "=VLOOKUP(A:A,'C:\MyDocuments\[Book1.xlsx]ws.Name'!$A:$B,2,FALSE)"
For Each ws In Worksheets
ws.Activate

With ActiveSheet.Cells
Range("G1").Select
Selection.Value = sString
End With
Next ws
End Sub
[/VBA]

[ntrauger]

Here's a suggestion that will fix your syntax:[vba]Sub Macro5()
Dim ws As Worksheet
Dim sString As String

sString = "=VLOOKUP(A:A,'C:\MyDocuments\[Book1.xlsx]" & ws.Name & "'!$A:$B,2,FALSE)"
For Each ws In Worksheets
ws.Activate

With ActiveSheet.Cells
Range("G1").Select
Selection.Value = sString
End With
Next ws
End Sub[/vba]