Sheet Names

Working With Sheet Names


Define current workbook name without being explicit

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
ThisWB = ThisWorkbook.Name
[/sourcecode]

To call the file, just use “ThisWB” in place of the real workbook name.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Windows("ThisWB").Activate
[/sourcecode]


Rename a worksheet

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
ActiveSheet.Select
ActiveSheet.Name = "TabName"
[/sourcecode]


Rename a worksheet based on cell contents

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
activesheet.name =[A1]
[/sourcecode]


Add Worksheet testing to be sure the name is not in use

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub AddSheet()
Dim ActNm As String

With ActiveWorkbook.Sheets
.Add after:=Worksheets(Worksheets.Count)
End With

ActNm = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = "Test"

NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give name.")

If ActiveSheet.Name = ActNm Then GoTo NoName
On Error GoTo 0

End Sub
[/sourcecode]

What it does
– Adds sheet after the last sheet which defaults to sheet(index number) (eg Sheet4)
This is stored in Variable ActNm so as to have a comparison.
– If the sheet exists then error 1004 generated and you are asked to input new name.
New name is comarppared to ActNm (usually Sheetx) if = then ask again if sheetname
not valid ask again.


Rename all worksheet tabs with each worksheet’s cell A1 contents

If cell A1 has no content, then that tab is not renamed.
[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub NameSheets()
Dim i as long
For i = 1 To Sheets.Count
If Worksheets(i).Range("A1").Value <> ""
Then Sheets(i).Name = Worksheets(i).Range("A1").Value
End If
Next
End Sub
[/sourcecode]

The followiong code lists all of the workbooks worksheets in the active sheet’s column A

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub ListSheetNames()
For Each wsh In ThisWorkbook.Worksheets
Range("A65536").End(xlUp).Offset(1, 0) = wsh.Name
Next wsh
End Sub
[/sourcecode]

This macro does the same thing as the one above, but adds hyperlinks to the actual worksheetssheets.
[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub ListSheetNames_Hyperlinks()
For Each wsh In ThisWorkbook.Worksheets
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:=wsh.Name & "!A1", TextToDisplay:=wsh.Name
Next wsh
End Sub
[/sourcecode]