Refer to the codename Sheet1 instead of the Tab name

I have recorded the following Macro:
Sub Mars()
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(Sheet1!RC[7]="""","""",TEXT(Sheet1!RC[7],""000000000000"")&TEXT(Sheet1!RC[8],""0000000000""))"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A2500"), Type:=xlFillDefault
End Sub

I would like to refer to the codename Sheet1 instead of the Tab name (in case the tab name changes), but I have been unable to figure this out. I have tried “Sheets1”, but when I run the Macro I am presented with windows Explorer.

Thank you for your help,
Lori
NLITechAsked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
Lori,

I only did that to make clear the difference between the sheet name and the codename. In the attached, I've renamed the codename back to "Sheet1" and changed the macro accordingly...
Option Explicit

Sub Mars()
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('" & Sheet1.Name & "'!RC[7]="""","""",TEXT('" & Sheet1.Name & "'!RC[7],""000000000000"")&TEXT('" & Sheet1.Name & "'!RC[8],""0000000000""))"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A2500"), Type:=xlFillDefault
End Sub

Open in new window

Regards,
Brian.CodeName-Formula-V3.xlsm
0
 
tdlewisCommented:
Press F4 to view the sheet properties and change the name:Sheet Properties
0
 
mark_harris231Commented:
Substitute Sheet1.Range() for Sheet1!

E.g., Sheet1.Range(RC[7])
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
NLITechAuthor Commented:
I am giving this Macro to other people. I want it to use the first tab regardless of what it is named.
0
 
NLITechAuthor Commented:
Mark,
Like this:
Sub Mars()
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(Sheet1.Range(RC[7])="""","""",TEXT Worksheets(Sheet1.Range(RC[7]),""000000000000"")&TEXT Worksheets(Sheet1.range(RC[8]),""0000000000""))"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A2500"), Type:=xlFillDefault
End Sub
?
0
 
redmondbCommented:
Hi, NLITech.

Please see attached. I rename Sheet1 and also gave it a Codename of "Fred". The macro will continue to work even if the Sheet's Name (not Codename!) is changed.

The code is...
Option Explicit

Sub Mars()
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('" & Fred.Name & "'!RC[7]="""","""",TEXT('" & Fred.Name & "'!RC[7],""000000000000"")&TEXT('" & Fred.Name & "'!RC[8],""0000000000""))"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A2500"), Type:=xlFillDefault
End Sub

Open in new window

Regards,
Brian.CodeName-Formula.xlsm
0
 
mark_harris231Commented:
Correct, NLITech.  That should do what you need, I think.
0
 
albeloCommented:
sheet1.select will select the index of the sheet
sheets(1).select will select the one to the far left.
0
 
NLITechAuthor Commented:
Mark,
That gives me #Name? as the result.

Brian,
I am still trying to comprehend, waiting for workbook to open.

tdlewis,
Not what I am looking for.
Thanks
0
 
redmondbCommented:
NLITech,

"waiting for workbook to open."
I downloaded the file and it's still working fine for me. Please restart Excel and try the file again.

Thanks,
Brian.
0
 
NLITechAuthor Commented:
albelo,
I get a Application-defined or object-refined error with:
ActiveCell.FormulaR1C1 = _
        "=IF(Sheets(1).select(RC[7])="""","""",TEXT Worksheets(Sheets(1).select(RC[7]),""000000000000"")&TEXT Worksheets(Sheets(1).select(RC[8]),""0000000000""))"
0
 
NLITechAuthor Commented:
Brian,
I do not wish to rename the code name, so I am not sure how to use this.
Lori
0
 
NLITechAuthor Commented:
Brian,
Thank you for dumb it down for me, I needed that. Worked perfectly!

Lori
0
 
redmondbCommented:
Thanks, Lori. Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.