asked on

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

tdlewis

Press F4 to view the sheet properties and change the name:

mark_harris231

Substitute Sheet1.Range() for Sheet1!

E.g., Sheet1.Range(RC[7])

NLITech

ASKER

I am giving this Macro to other people. I want it to use the first tab regardless of what it is named.

NLITech

ASKER

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
?

redmondb

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

mark_harris231

Correct, NLITech. That should do what you need, I think.

albelo

sheet1.select will select the index of the sheet
sheets(1).select will select the one to the far left.

NLITech

ASKER

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

redmondb

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.

NLITech

ASKER

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""))"

NLITech

ASKER

Brian,
I do not wish to rename the code name, so I am not sure how to use this.
Lori

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NLITech

ASKER

Brian,
Thank you for dumb it down for me, I needed that. Worked perfectly!

Lori

redmondb

Thanks, Lori. Glad to help.