• 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.


4 Thoughts on “VBA-Excel: Get the names of all WorkSheets in a Excel (WorkBook)

  1. Hello,
    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 on April 20, 2014 at 7:03 am said:

      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 ..in your case to print the sub work­book sheet names

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

  2. Hello,

    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 tutorialhorizon.com’s con­tent man­age­ment software.

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



    • sumitjain on May 10, 2014 at 4:23 am said:

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


Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation