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
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