Sheet CodeNames

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
 
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 "!".)
 
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
-
 
Thanks to you both!

- said:
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
-
Click to expand...
 
<<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?
 
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.
 
Back
Top