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

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

Expla­na­tion:

mainworkBook.Sheets.count

Sheets.count will give you the num­ber 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

Cre­ate a Work­Book at Runtime

Open a Excel WorkBook

WorkBook.Save Method

Cre­ate Dynamic or Run-time Work­Sheets using Macro

Delete Blank Rows from Excel Work Sheet

Get the names of all Work­Sheets in a Excel (WorkBook)

Happy Macro­ing :)

Sumit Jain

 

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

  1. Ian

    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

    1. sumitjain Post author

      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
      Sumit

  2. D

    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.

    Thanks,

    David

    1. sumitjain Post author

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

      Thanks
      Sumit

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>

%d bloggers like this: