3 Ways to Get a Sheet Name and Rename a Sheet Using VBA (Code Included)

Опубликовано: 06 Октябрь 2024
на канале: EverydayVBA

5,109

**For more Topics Like this
https://chrisjterrell.com/blog/223404...
**Grab the Free VBA Quick Reference Guide
https://www.chrisjterrell.com/excel-v...

In this video, we show you how to get the name of a sheet using three ways. We get the name using the Object Name or Code Name. Then we use the name of the sheet as a reference to get the index of the sheet. The sheet index is the value that represents the number sheet number from left to right in a workbook. Keep in mind that hidden sheets have an index. Additionally, reordering sheets reorder changes the index. Lastly, we get the CodeName or Object Name using the Sheet Index.

Then we rename each sheet using the three options above. We rename the first sheet using the index, we rename the second sheet by referencing the sheet name, and we rename the last sheet using the object reference.

Then we run our original macro, and it produces expected errors when referencing the sheet names because they have now changed.

‘======================
‘=======CODE==========
‘======================

Sub SheetName()

Range("I12") = Sheet1.Name
Range("I13") = Sheet3.Name
Range("I14") = Sheet2.Name

Range("J12") = Sheets("Sheet1 Name").Index
Range("J13") = Sheets("Sheet3 Name").Index
Range("J14") = Sheets("Sheet2 Name").Index

Range("K12") = Sheets(1).CodeName
Range("K13") = Sheets(2).CodeName
Range("K14") = Sheets(3).CodeName

End Sub

Sub SheetRename()

Sheets(1).Name = "By Index"
Sheets("Sheet3 Name").Name = "By Name"
Sheet2.Name = "By Ojbect-CodeName"

End Sub