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?
Function MySheet() ' uncomment the below line to make it Volatile 'Application.Volatile MySheet = Application.Caller.Worksheet.Name End Function
This should be the function you are looking for
This works for me.
worksheetName = ActiveSheet.Name
Read more... Read less...
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 xNext_SelectSheet: Next xSheet MsgBox "Show Selected Sheet(s) Completed !!!" end sub