Excel macro to refer to a cell via a variable

[Panos_user]

Hi,

My name is Panos, I am new to the forum and excel as well. Currently I am using Windows Vista with excel 2007.
I have an excel file with several sheets. On the first sheet (summary page) there is a macro that creates new sheets, also letting users insert the name of that sheet in a pop-up box. The name of the sheet is saved to a variable called "ProjName".
What I am not able to do is write a macro that will take the value of the variable along with the content of a certain cell and put it back on the summary page. Can anyone please advice?
Please fine the code I am using below

Sub Project_Name()
Dim ProjName As String
ProjName = InputBox("Please enter your Project Name. Do not use special charactors such as !%$&*", "Project Name Entry Form", "Enter your Project Name here", 500, 700)
Sheets("Project Plan Template").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = ProjName
Worksheets(ProjName).Range("C2") = ProjName

'select first sheet/ find last row/ insert new row and copy values
Sheets("Current Projects").Select
Range("A1").Select
Range("B8:S8").Select
Selection.Copy
Cells(Cells(Rows.Count, "B").End(xlUp).Row + 1, "B").Select
ActiveSheet.Paste

'get value from ProjName variable and insert references
Cells(Cells(Rows.Count, "B").End(xlUp).Row, "B").Select
ActiveCell.FormulaR1C1 = "test"
Cells(Cells(Rows.Count, "C").End(xlUp).Row, "C").Select
ActiveCell.FormulaR1C1 = "='China Transport PUD'!R3C3"
Cells(Cells(Rows.Count, "D").End(xlUp).Row, "D").Select
ActiveCell.FormulaR1C1 = ProjName
Cells(Cells(Rows.Count, "E").End(xlUp).Row, "E").Select
ActiveCell.FormulaR1C1 = ProjName & "!R3C3"
End Sub

I tried testinga couple of ideas and the best i had are above. I hope that this only is a syntax error and not something more serious...

Thank you in advance for your help,

Regards,

Panos

 

[RoryA]

Basic syntax would be:

ActiveCell.FormulaR1C1 = "='" & ProjName & "'!R3C3"