Sheet1/Sheets(1)/Sheets("sheet1")

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,516
Hi
I have one sheet in a workbooks, and that sheet I can call it

1) Sheet1
2) Sheets(1) or Worksheets(1)
3) Sheets("sheet1") or Worksheets("sheet1")

Sheet1, Sheets("sheet1") or Worksheets("sheet1") will always refer to that sheet no matter what except if the sheet name has been changed then only Sheet1 will refer to that sheet

Sheets(1) or Worksheets(1), will always refer to that sheet unless its tab physically move to different location.

So Sheet1 will always refer to that sheet no matter what, if name is changed or location. So I can use that name in my code to avoid any problem from changing name and location. Am I right
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
Right with respect to tab name changes or tab order changes. Excel assigns code names in the format Sheetn where n = 1,2,3,.... depending on the order in which the sheet was created. It is possible to change the code name directly on the VBA Properties menu for the sheet where it's listed as (Name).
 
Last edited:

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,516
wow so happy to know I can change the codeName as well. Thank you so much.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
You're welcome. I posted an edit to my initial reply while you were posting post #3 . Go back and read the edited post #2 . I had forgotten that the CodeName property of a sheet is read only. That limits changing the code name to using the VBA properties window for the sheet.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
You should use Sheets(1) as that will refer to the only sheet in the workbook even if it's tab name or codename has been changed.
 

Forum statistics

Threads
1,082,133
Messages
5,363,341
Members
400,728
Latest member
Hoan1985

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top