Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Get the names of all WorkSheets in a Excel (WorkBook)

  • 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

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:

You may also like...

7 Responses

  1. Ian says:

    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

    • sumitjain says:

      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

  2. D says:

    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

  3. venkat says:

    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 ??

  4. Amit says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: