VLOOKUP - Copy cell, change cell reference, paste formula, problem with VLOOKUP sheet name reference

[plannett]

Hello experts,
I am using the data mining tools from MS. One of the tools gives me a table of data. I need to expand what the table provides.
Therefore, I want to copy the formula they give me, modify it and then paste to another cell. I tried the =GetFormula UDF but still couldn't get what I need.
I am using VLOOKUP for now.
I created a macro. I copy the cell referenced usnig a Const.
I change the cell reference as I only need to do this for the columns, not the rows.
I'll have approximately 10 of these.
Instead of the sheet name being hardcoded, I would like to reference it, but I cannot figure out how to do this.
This way, when the sheet name changes, which it will, i want to change it once in the Const referenced instead of all of the times it is hardcoded.
Is there a way to do this?
Perhaps I am just getting the syntax wrong?

Const SheetNameHere as string = "SheetNameTest1"

Sub Test()
ActiveWorkbook.Activate
Range(RangeName1).Select
Selection.Copy
Range(RangeName1).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP('SheetNameHere'!R16C2, 'SheetNameHere'!R35C15:R63C16, 2, FALSE)"
Application.CutCopyMode = False
End Sub

Thanks for any help or advice you can offer.

 

[plannett]

Another alternative as I think about this, is to have a formula change the cell reference from the source cell in the destination cell.

Source cell
=VLOOKUP(PCT!$B$16, PCT!$B$35:$C$63, 2, FALSE)
I want the destionation cell to be
=VLOOKUP(PCT!$O$16, PCT!$O$35:$P$63, 2, FALSE)
and I want a formula that can change this for me.

Source cell changes
$B (first instance) to $O
$B (second instance) to $O
$C to $P

is there a formula I can embed inside vlookup that will read the first formula and change/substitute the characters necessary?
I tried substitute and could not get it to work
I tried =GetFormula in an adjacent cell so it was text and then have the destionation cell read the text in the adjacent cell but I also couldn't get that to work.
I'm not married to any solution and would prefer a formula over VBA but I'll take whatever works.

Thanks again.

 

[plannett]

Actually, consider this closed. I just got this working using the =GetFormula UDF.
http://dmcritchie.mvps.org/excel/formula.htm#getformula

I used getformula and put the text of the formula in an adjacent cell.
Then I used =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE....))), to do what I needed
I then made another adjacent cell that = the SUBSTITUTE formula
I copy and paste that cell as values, then F2 to edit, then enter and it converts it to the Formula I need.
A quick and easy macro will copy and paste as values, F2 to edit and then enter to give me the formula I need.
From there autofill work work with the rest of the table, which will be included in the macro.