Just do what Excel does if you reference that sheet from another sheet....
Double the single-quote, like this example:

ActiveCell.Formula = "='O'' RAMBO'!A1"

or...if you're reading the sheet name from the tab...
replace single-quotes with doubles...like this:
ActiveCell.Formula = "='" & Replace(Sheet4.Name, "'", "''") & "'!A1"

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

"ankur" wrote in message
ps.com...

Hi Experts,


I am actually wrting a program which will populate some of the cells
in sheet 1 with a formula which looks like .... "=John!A1+David!
A1+Smith!A1"

I am simply trying to add cell A1 values in various sheets which are
named as individual names.

The problem comes when some one's name is like O' RAMBO etc...

I think case the formula throws an error because formual does't
take '

Any workaround for this one?


Thanks A Lot in advance.

Regards
Ankur