You are here:

Excel/Getting Sheet Name from Unique Sheet Number


QUESTION: Good day Jan,
Tried several things but unsuccessfull!!!

I want to place in WkshtName variable the name found on the TAB of the unique sheet4 (created by Excel).

Dim WkshtName As String
WkshtName = Sheets(4).Name 'returning the 4th sheet in the workbook - No Good
WkshtName = Sheet4.Name - error (object required)

Thank you / Merci

ANSWER: Hi Daniel,

I'm not what you're after. You could directly do this:

WkshtName ="Sheet4"

Or if it is the 4th worksheet (chart sheets also count as sheets...):

WkshtName = Worksheets(4).Name

---------- FOLLOW-UP ----------

Sorry for not being clear enough...

In the Project - VBAProject window, we can see all sheets composing the workbook.  All sheets are listed as:
Sheet24 (PCR late)
Sheet12 (Wiring Status)

When we add another Sheet, Excel provide a UNIQUE number to it (Sheet24 or Sheet12 in the exemple above).

The name (PCR late, Wiring Missing) can change BUT the number (24 or 12)remains the same as it is unique.

I do not want to code the name {sheets("Wiring Status")...} in my macro as it might change NOR I do not want to call sheet(12) as it is not self-explanatory.

At the beginning of the macro, I want to place in the variable "wkshtName", the name of the Sheet12 since I know it cannot be changed and later refer to it in the code.

Hi daniel,

The solution depends on your situation actually. If this is a worksheet you yourself
insert(ed) and control, you can change that "unique" name (technically referred to as the sheet's CodeName) yourself by editing the name property of the Sheet12 object: click the Sheet12 entry in the project explorer and type a new "CodeName" in the (Name) box in the properties window. Then hit enter. After that, you can refer to the sheet with your new codename. No variable needed.

If however this is a sheet inserted by someone else (you have no control over) then I would not know anything else then either referring to the sheet using its visible tabname, or by its index.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Jan Karel Pieterse


Excel and Excel/VBA questions


Excel MVP

Self employed Excel developer

Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2016 All rights reserved.