Get a worksheet name using Excel VBA


I would like to create an user-defined function in Excel that can return the current worksheet. I could use the

sheetname = ActiveSheet.Name

But the problem with this is, it works and suddenly it starts to get different sheet name. For example, instead of SHEET I LOVE YOU it returns SHEET I HATE YOU.

Is there anyway to fix this - or it might possible because I think it can not be static but varies?

7/9/2018 6:41:45 PM

Accepted Answer

Function MySheet()

  ' uncomment the below line to make it Volatile
   MySheet = Application.Caller.Worksheet.Name

End Function

This should be the function you are looking for

10/14/2019 2:20:33 PM

Sub FnGetSheetsName()

    Dim mainworkBook As Workbook

    Set mainworkBook = ActiveWorkbook

    For i = 1 To mainworkBook.Sheets.Count

    'Either we can put all names in an array , here we are printing all the names in Sheet 2

    mainworkBook.Sheets("Sheet2").Range("A" & i) = mainworkBook.Sheets(i).Name

    Next i

End Sub

You can use below code to get the Active Sheet name and change it to yours preferred name.

Sub ChangeSheetName()

Dim shName As String
Dim currentName As String
currentName = ActiveSheet.Name
shName = InputBox("What name you want to give for your sheet")
ThisWorkbook.Sheets(currentName).Name = shName

End Sub

Extend Code for Show Selected Sheet(s) [ one or more sheets].

Sub Show_SelectSheet()
  For Each xSheet In ThisWorkbook.Worksheets
     For Each xSelectSheet In ActiveWindow.SelectedSheets
         If xSheet.Name = xSelectSheet.Name Then
            '=== Show Selected Sheet ===
            GoTo xNext_SelectSheet
         End If
     Next xSelectSheet
  xSheet.Visible = False  
Next xSheet
MsgBox "Show Selected Sheet(s) Completed !!!"
end sub