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

Not tested but something like

Dim nws as string
For Each x In activeworkbook.Worksheets
x.Range("AA1:AA1").Formula = "='F:\[Contacts 2008.xls]" & x.name &
"'!$A1"
Next x


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

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

Are you saying the code I posted didn't work? Did you copy/paste it or
re-type it? If you re-typed it, did you notice the apostrophe next to the
quote mark that is after the last ampersand?

Rick


"Fred" wrote in message
...
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

I retyped the code, and I did notice the single apostrophe. Copy/paste
didn't make a difference. If I remove the 4 double quotes, I get compile
errors. If I remove the 2 double quotes from either side of the ampersands,
then it prompts me for the workbook to use each time. If I leave all quotes
in, it works, although it is slow.

"Rick Rothstein (MVP - VB)" wrote:

Are you saying the code I posted didn't work? Did you copy/paste it or
re-type it? If you re-typed it, did you notice the apostrophe next to the
quote mark that is after the last ampersand?

Rick


"Fred" wrote in message
...
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

Okay, then the code I gave you is working... I wasn't completely sure what
you are saying in your last posting. As for the quote marks... yes, the
quote marks, as I listed them, are required by syntax... you can't remove or
modify them as they delineate the non-changing portions of your text.

Rick


"Fred" wrote in message
...
I retyped the code, and I did notice the single apostrophe. Copy/paste
didn't make a difference. If I remove the 4 double quotes, I get compile
errors. If I remove the 2 double quotes from either side of the
ampersands,
then it prompts me for the workbook to use each time. If I leave all
quotes
in, it works, although it is slow.

"Rick Rothstein (MVP - VB)" wrote:

Are you saying the code I posted didn't work? Did you copy/paste it or
re-type it? If you re-typed it, did you notice the apostrophe next to the
quote mark that is after the last ampersand?

Rick


"Fred" wrote in message
...
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

Yes, it is working. Thank you for your help.
Fred

"Rick Rothstein (MVP - VB)" wrote:

Okay, then the code I gave you is working... I wasn't completely sure what
you are saying in your last posting. As for the quote marks... yes, the
quote marks, as I listed them, are required by syntax... you can't remove or
modify them as they delineate the non-changing portions of your text.

Rick


"Fred" wrote in message
...
I retyped the code, and I did notice the single apostrophe. Copy/paste
didn't make a difference. If I remove the 4 double quotes, I get compile
errors. If I remove the 2 double quotes from either side of the
ampersands,
then it prompts me for the workbook to use each time. If I leave all
quotes
in, it works, although it is slow.

"Rick Rothstein (MVP - VB)" wrote:

Are you saying the code I posted didn't work? Did you copy/paste it or
re-type it? If you re-typed it, did you notice the apostrophe next to the
quote mark that is after the last ampersand?

Rick


"Fred" wrote in message
...
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