Hi,

If I am using a worksheet codename, how would I use it in this formula?

Range("A10").Select
ActivateCell.formulaR1C1 = "='Mainsheet'!R10C22"

The sheet's code name is MainPage. I have tried leaving out the single
quotes i.e.

ActivateCell.FormulaR1C1 = "=MainPage!R10C22"

but it doesn't work. How would I use a sheet code name in this instance?

Thanks for your help.

Formulas don't refer to the code names. You could do this though:

Range("A10").FormulaR1C1 = "='" & Replace(MainPage.Name, "'", "''") &
"'!R10C22"




"Karen53" wrote in message
...
Hi,

If I am using a worksheet codename, how would I use it in this formula?

Range("A10").Select
ActivateCell.formulaR1C1 = "='Mainsheet'!R10C22"

The sheet's code name is MainPage. I have tried leaving out the single
quotes i.e.

ActivateCell.FormulaR1C1 = "=MainPage!R10C22"

but it doesn't work. How would I use a sheet code name in this instance?

Thanks for your help.

ActivateCell.FormulaR1C1 = "='" & MainPage.Name & "'!R10C22"

MainPage refers to the worksheet (code name) as you requested, and the
".Name" part is the property that returns the tab name (what the user
sees at the bottom of the worksheet). Be sure to include single quotes
around the sheet name, in case the sheet name has spaces in it. (Note
the single quote to the right of the second "=" and just to the left of
the "!".)
--
Regards,
Bill Renaud

That will fail if there are single quotes in the sheet name. Better for the
OP to use what I posted in the event there are single quotes in the sheet
name, which need to be delimited.

Cheers
-

"Bill Renaud" wrote in message
. ..
ActivateCell.FormulaR1C1 = "='" & MainPage.Name & "'!R10C22"

MainPage refers to the worksheet (code name) as you requested, and the
".Name" part is the property that returns the tab name (what the user
sees at the bottom of the worksheet). Be sure to include single quotes
around the sheet name, in case the sheet name has spaces in it. (Note
the single quote to the right of the second "=" and just to the left of
the "!".)
--
Regards,
Bill Renaud

Thanks to you both!

"-" wrote:

That will fail if there are single quotes in the sheet name. Better for the
OP to use what I posted in the event there are single quotes in the sheet
name, which need to be delimited.

Cheers
-

"Bill Renaud" wrote in message
. ..
ActivateCell.FormulaR1C1 = "='" & MainPage.Name & "'!R10C22"

MainPage refers to the worksheet (code name) as you requested, and the
".Name" part is the property that returns the tab name (what the user
sees at the bottom of the worksheet). Be sure to include single quotes
around the sheet name, in case the sheet name has spaces in it. (Note
the single quote to the right of the second "=" and just to the left of
the "!".)
--
Regards,
Bill Renaud

<<That will fail if there are single quotes in the sheet name.
Excel 200 does not allow single quotes in the sheet name. Does Excel
2007 allow this?
--
Regards,
Bill Renaud

My copies of XL2000 and XL2003 do allow single quotes. Always have.

"Bill Renaud" wrote in message
. ..
<<That will fail if there are single quotes in the sheet name.
Excel 200 does not allow single quotes in the sheet name. Does Excel
2007 allow this?
--
Regards,
Bill Renaud

Strange. My copy of Excel 2000 (with SP3) does not allow quotes in
either the first or last character positions. It only allows them as
long as they are embedded in the middle of the tab name.

You are correct about needing to remove any single quotes from the sheet
name before using it in the formula.
--
Regards,
Bill Renaud