Announcement

Collapse
No announcement yet.

List Names Of Sheets in Workbook Into Array

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • List Names Of Sheets in Workbook Into Array

    Hi,

    Does anyone know a good way to generate a list (i.e. array) of tabs available in the current spreadsheet?

    Thanks,

    Jonas

  • #2
    Re: List Of Available Tabs In Vba

    I did a search for list worksheets" and came up with a thread where Dave answered a similar question here.
    There are some ideas so preposterous that only an intellectual will believe them. - Malcolm Muggeridge

    Comment


    • #3
      Re: List Of Available Tabs In Vba

      Run this code on any worksheet where Column A is not used.
      Code:
      Sub AllSheetToArray()
      Dim StrSheets() As String
      Dim OSheet As Object
      Dim lCount As Long
      
          ReDim StrSheets(Sheets.Count - 1)
          
          For Each OSheet In Sheets
              StrSheets(lCount) = OSheet.Name
              lCount = lCount + 1
          Next OSheet
          
          For lCount = LBound(StrSheets) To UBound(StrSheets)
              Cells(lCount + 1, 1) = StrSheets(lCount)
          Next lCount
      
      End Sub

      Comment


      • #4
        Re: List Of Available Tabs In Vba

        Hi,

        Sub ListSheets()
        Dim i As Byte, j As Byte
        Dim Sht()

        ReDim Sht(ThisWorkbook.Sheets.Count)
        For i = 1 To Sheets.Count
        Sht(j) = Sheets(i).Name
        j = j + 1
        Next i
        ReDim Preserve Sht(j)
        [a1].Resize(UBound(Sht), 1) = Application.Transpose(Sht)
        End Sub


        HTH
        Kris

        ExcelFox

        Comment

        Working...
        X