Lookup based on all sheets in workbook

[krispi]

All - I am trying to do a lookup based on the same range for all worksheets in a workbook, and return the results to another workbook (one column of results per worksheet). I'm just trying to get some test code to work at the moment, but (based on Watches) it's falling over when it gets to the line 'For Each ws In wb2' - any advice as to why would be gratefully appreciated...

Private Sub test1()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim intR As Integer
Dim intC As Integer
Dim LURange As Range
Dim LUValue As String
Dim strVal As String
Dim myVar As Variant

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("J:\Chris\Automation\Test1.xls")
intC = 2
For Each ws In wb2
     Set LURange = ws.Range("A:B")
        intR = 1
            Do While intR < 4
                LUValue = wb1.Worksheets("Sheet1").Cells(intR, 1)
                myVar = Application.VLookup(LUValue, LURange, 2, False)
                wb1.Worksheets("sheet1").Cells(intR, intC).Value = myVar
                intR = intR + 1
            Loop
        intC = intC + 1
    Next ws
End Sub

 

[DaveInIowa]

For Each ws In wb2[red].Worksheets[/red]

 

[krispi]

Dave it's been a long day! Thanks for saving my sanity!!!