Be the first user to complete this post

  • 0
Add to List

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:



Also Read:

  1. VBA-Excel: Create worksheets with Names in Specific Format/Pattern.
  2. VBA-Excel: UsedRange
  3. VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One
  4. VBA Excel - Cells, Ranges and Offset : Range
  5. VBA-Excel: Modified Consolidator – Merge or Combine Multiple Excel Files Into One Where Columns Are Not In Order
  6. VBA-Excel: Application.Wait OR Wait Method
  7. VBA Excel – Refer to Multiple Ranges : Union Method
  8. VBA-Excel: Open a Excel WorkBook