CODEWITHSUNDEEP
vbaexcel
MCJNY1992
MCJNY1992

Reputation: 107

How to rename sheets

How do I rename a sheet by referencing its position? That is, if I want to rename the second sheet, without using a code such as:

Sheets("Sheet1").Name = "New Name"

As this creates problems when my code adds sheets, and if in the past a sheet was deleted from the workbook it won't necessarily correspond to the next number in line.

Example: if I delete Sheet1 and then create a new one it will be Sheet2, but for the sake of not having to change the macro every time I use it I just want to reference to the position of the tab. And say the firstSheet, rename to "New Name".

Is that possible or is there a workaround?

Upvotes: 1

Views: 158

Answers (1)

David Zemens
David Zemens

Reputation: 53623

The Sheets collection is indexed by an index (position) or name, both of which are unique. For a new workbook, these should result the same:

MsgBox Sheets("Sheet1").Name
MsgBox Sheets(1).Name

So, for your question:

How do I rename a sheet by referencing it's position?

Use the index of the sheet.

Sheets(_index_).Name = "New Name"

Upvotes: 1

Related Questions