with activesheet
Myformula = .Range("G1").formula
NewSheet = .Range("A1")

'remove old sheet name
Mid(Myformula,instr(Myformula,"!"))
'Add New sheet name to formula
'Note the 1st string is an double quote, colon, single quote, and double quote
'the last string is a double quote, single quote, double quote
Myformula = "='" & NewSheet & "'" & Myformula
'return new formula to worksheet
..Range("G1").formula = Myformula



end with

"Harn88" wrote:

Dear Joel

Thank you for your reply

='Brisbane Metro'!G12 is referring to a worksheet Tab, so "Brisbane Metro"
is a tab in the workbook. Gold Coast is also another tab in a workbook.

I want to be able to replace ='Brisbane Metro'!G12 tab to ='Gold Coast'!G12
tab and return the number in that tab.

In another word I want a Marco to replace the word 'Brisbane Metro' to 'Gold
Coast'! According to what input in a cell for example A1.

I hope I explain it well, please let me know if you need more information

Thank you very much!

Best regards,

Harn


"joel" wrote:

Why not use Indirect?

from
='Brisbane Metro'!G12
to
=INDIRECT("'" & A1 & "'!G12")

"Harn88" wrote:

Hi

Please help me; I need help with a Marco.

I want to be able to change a certain word in the formula according to what
is written in the cell.

For example:

If a formula in Cell A3 is as follow:

='Brisbane Metro'!G12

However if I wrote €˜Gold Coast€� in Cell A1 it will change the above formula
in Cell A3 from

='Brisbane Metro'!G12

To

='Gold Coast!G12

Thank you very much for your help!!!

Please let me know if you need more information

Best regards,

Harn