Thanks Rick. Works great with quote marks, doesn't work without them.

Fred


"Rick Rothstein (MVP - VB)" wrote:

Try this...

ActiveCell.Formula = "='F:\[Contacts 2008.xls]" & nws & "'!$A1"

Just a note... anything between two quote marks is pure text; if you want it
to be a variable, it must be concatenated onto the other text parts so that
it is not inside a pair of quote marks.

Rick



"Fred" wrote in message
...
Hi
I have a "Contacts" workbook with 12 worksheets in it, one for each month.
I also have a "Vendor" worksheet, also with 12 worksheets in it. I need
to
change the formula in cell AA1 of the Vendor worksheet Jan 08 to read the
info in cell A1 on the Jan 08 Contacts worksheet. For the next month, the
formula needs to move to the Feb 08 sheets, and so on.

How can I use the sheetname variable "nws" in a formula to reference the
sheet and enter in the sheetname at that spot?

Here is a bit of the code that I have for this part:

Dim nws as string
For Each x In Worksheets
x.Activate
nws = ActiveSheet.Name
Range("AA1:AA1").Select
ActiveCell.Formula = "='F:\[Contacts 2008.xls]nws'!$A1"
Next x

Thank you
Fred