PDA

View Full Version : Vlookup multiple worksheets between two workbook



yoitsmejy
05-05-2011, 10:31 AM
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.

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

ntrauger
05-05-2011, 12:36 PM
Here's a suggestion that will fix your syntax: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