Excel macro to refer to a cell via a variable

Panos_user

New Member
Joined
Mar 21, 2011
Messages
1
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,503
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Basic syntax would be:
Code:
ActiveCell.FormulaR1C1 = "='" & ProjName & "'!R3C3"
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top