Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: List Names Of Sheets in Workbook Into Array

  1. #1
    Join Date
    19th October 2006
    Posts
    3

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th February 2005
    Location
    Texas
    Posts
    251

    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

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: List Of Available Tabs In Vba

    Run this code on any worksheet where Column A is not used.
    VB:
    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 
    
    

  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: List Of Available Tabs In Vba

    Hi,

    VB:
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. List Names Of Workbook Files In Folder
    By dannZ in forum Excel General
    Replies: 9
    Last Post: September 27th, 2013, 07:50
  2. List All Worksheet Names Of Workbook
    By kherbert in forum Excel General
    Replies: 4
    Last Post: March 23rd, 2013, 00:20
  3. Replies: 1
    Last Post: May 12th, 2008, 10:39
  4. Create Sheets With Month Names By Date List
    By J.B. in forum Excel General
    Replies: 6
    Last Post: January 11th, 2008, 14:15
  5. List all names in workbook
    By critcho in forum Excel General
    Replies: 4
    Last Post: May 17th, 2005, 08:05

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno