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 Work­Book and press “Alt+F11” to open the Visual Basic Editor
  • Copy Paste the fol­low­ing 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 Work­Sheets Names



Sheets.count will give you the num­ber of sheets present in the Excel (WorkBook)


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:

    I have a MainWB and with codes to open subWB, and i would like to get the sheet­names of sub work­book when it is opened, and list them in MainWB Sheet“Sheet1” Range“A1”, how to do this? and every­time a new sub WB is opened the MainWB Range list is updated, thank you for help

    • sumitjain says:

      Store the mainWB instance in one vari­able.
      dim mainwb as work­book.
      Dim subwb
      Set mainwb = ActiveWorkBook

      ’ code to open ur sub workbook

      Set subwb = ActiveWorkBook

      Use the arti­cle code on this subwb. And store the name in mainwb.range(A1)…

      Idea is to store the one work­book instance before u open another so the you can use it later your case to print the sub work­book sheet names

      Feel free to ask if this solu­tion doesn’t work for u

  2. D says:


    I liked the page. I hope you won’t mind two minor criticisms.

    - My VBA edi­tor was con­fused by the com­ment char­ac­ter (‘) which appeared on the web page as ‘ (Uni­code LEFT SINGLE QUOTATION MARK). This might be an arti­fact of’s con­tent man­age­ment software.

    - I got con­fused by the non-‘dim’ed loop index I.



    • sumitjain says:

      Thanks David, I appre­ci­ate your com­ments, will try not to repeat in fur­ther posts.


  3. venkat says:

    Dear Sir,
    I have a work­book with sheets named as FY_2013-14 , FY_2014-15. Now when I want to cre­ate a new sheet with the name FY_2015-16 using an input­box vari­able and more­over the vari­able to accept the input only if the name is type out in the set for­mat i.e FY_xxxx-xx” . How to solve this issue ??

  4. Amit says:

    Sub FnGet­Sheet­sName()

    Dim main­work­Book As Workbook

    Set main­work­Book = ActiveWorkbook

    For i = 1 To mainworkBook.Sheets.Count

    Either we can put all names in an array , here we are print­ing 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: