• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

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
0
NLITech
Asked:
NLITech
  • 6
  • 4
  • 2
  • +2
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
redmondbCommented:
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now