- Open a new Excel WorkBook and press “Alt+F11” to open the Visual Basic Editor
- Copy Paste the following code
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
- Run the Macro
All WorkSheets Names
Explanation:
mainworkBook.Sheets.count
Sheets.count will give you the number of sheets present in the Excel (WorkBook)
mainworkBook.Sheets(i).Name
Sheets(i).Name will fetch the Sheet Name based upon the index value, which is ‘i’ here.
Also Read about:
Hello,
I have a MainWB and with codes to open subWB, and i would like to get the sheetnames of sub workbook when it is opened, and list them in MainWB Sheet”Sheet1″ Range”A1″, how to do this? and everytime a new sub WB is opened the MainWB Range list is updated, thank you for help
Store the mainWB instance in one variable.
dim mainwb as workbook.
Dim subwb
Set mainwb = ActiveWorkBook
‘ code to open ur sub workbook
Set subwb = ActiveWorkBook
Use the article code on this subwb. And store the name in mainwb.range(A1)…
Idea is to store the one workbook instance before u open another so the you can use it later ..in your case to print the sub workbook sheet names
Feel free to ask if this solution doesn’t work for u
Sumit
Hello,
I liked the page. I hope you won’t mind two minor criticisms.
– My VBA editor was confused by the comment character (‘) which appeared on the web page as ‘ (Unicode LEFT SINGLE QUOTATION MARK). This might be an artifact of tutorialhorizon.com’s content management software.
– I got confused by the non-‘dim’ed loop index I.
Thanks,
David
Thanks David, I appreciate your comments, will try not to repeat in further posts.
Thanks
Sumit
Dear Sir,
I have a workbook with sheets named as FY_2013-14 , FY_2014-15. Now when I want to create a new sheet with the name FY_2015-16 using an inputbox variable and moreover the variable to accept the input only if the name is type out in the set format i.e FY_xxxx-xx” . How to solve this issue ??
Hello venkat, Please refer the link below
http://excel-macro.tutorialhorizon.com/vba-excel-create-worksheets-with-names-in-specific-formatpattern/
Do let me know if you have any questions
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