I have this as my code:
Workbooks("Auto Model Grid").Worksheets("Sheet1").Activate
With ActiveSheet

For counter = 2 To 55
Set mycell = Worksheets("Sheet1").Cells(counter, 12)

lval = mycell
If lval = "CCS" Then
If ccsfile = "yes" Then
mycell.Offset(counter, 14).Value =
Application.Worksheet.VLookup("CCS" & dlrrep,
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If

ElseIf lval = "DCS" Then
If dcsfile = "yes" Then
mycell.Offset(counter, 14).Value =
Application.WorksheetFunction.VLookup("DCS" & dlrrep,
Sheets("SMART").Range("$A$2:$H$82"), 7, 0)
End If
End If
Next counter
End With

For some reason when I do the vlookup I am getting an error message saying
the subscript is out of range. Is this because I am in another workbook that
it can't do the lookup on the other workbook or is it because I am trying to
string together a string plus a variable as the workbook's name? Any
thoughts would be welcome.
Thanks again in advance.
Heather O