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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

RoryA

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,945
Members
430,329
Latest member
asmith75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top