set myworkbook=workbooks(wokrbooks.count)


Does this give you the right workbook? Is it the last workbook opened by
the user, or opened by your code?

then in my VLOOKUP formula
=VLOOKUP(A:A,[myworkbook]CA!$A:$B,2)


Are you setting this formula in code? If so, the formula is a string and
you can build that string. If the formula looks like this

=VLOOKUP(A:A,[MyBook.xls]CA!$A:$B,2)

then your VBA would look like

Range("a1").Formula = "=VLOOKUP(A:A,[" & myworkbook.Name & "]CA!$A:$B,2)"


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.