Results 1 to 4 of 4

Thread: List the names of sheets in an Excel document

  1. #1
    Join Date
    Jun 2009
    Posts
    87

    List the names of sheets in an Excel document

    I searched everywhere and I can not find a way to list all sheets of an Excel document. For example: Sheet1, sheet2 and finally Sheet 3. And I'd like to list them automatically in a worksheet:
    A1 = Sheet1
    A2 = Sheet2
    A3 = Sheet3
    etc...

    It's just for a list of sheets in an excel workbook that has so.

  2. #2
    Join Date
    Apr 2008
    Posts
    2,010

    Re: List the names of sheets in an Excel document

    You can definitely do that and it is very simple. Simply code something like as below:

    Code:
    For x = 1 To Excel.Workbooks(1).Worksheets.Count 
      MsgBox (Excel.Worksheets(x).Name) 
    Next

  3. #3
    Join Date
    May 2008
    Posts
    685

    Re: List the names of sheets in an Excel document

    Code:
    Option Explicit 
     
    Dim Ar() As String 
     
    Sub Tst() 
    Dim Ws As Worksheet, i As Integer 
    Dim Ch As Chart 
        With ShTst 
            .Activate 
            .Cells.Clear 
        End With 
        i = 0 
        For Each Ws In ThisWorkbook.Worksheets 
            i = i + 1 
            With ShTst 
                .Range("A" & i) = Ws.Name 
                .Range("B" & i) = Ws.CodeName 
            End With 
        Next Ws 
     
        For Each Ch In ThisWorkbook.Charts 
            i = i + 1 
            With ShTst 
                .Range("A" & i) = Ch.Name 
                .Range("B" & i) = Ch.CodeName 
            End With 
        Next Ch 
    End Sub 
     
    Sub Tst2() 
        With ShTst 
            .Activate 
            .Cells.Clear 
        End With 
        ShTst.Range("A1:A" & UBound(Ar) + 1) = NameSheets
    End Sub 
     
    Private Function NameSheets() 
    Dim i As Integer 
        ReDim Ar(ThisWorkbook.Sheets.Count - 1) 
        For i = 0 To ThisWorkbook.Sheets.Count - 1 
            Ar(i) = Sheets(i + 1).Name 
        Next i 
        NameSheets = Application.WorksheetFunction.Transpose(Ar) 
    End Function

  4. #4
    Join Date
    Oct 2005
    Posts
    2,394

    Re: List the names of sheets in an Excel document

    Here is a short macro to respond to your request, if ever you had not yet found, and possibly to serve others. You select from Sub to End Sub with the mouse, you do Ctrl + C and Ctrl + V in a module, and you throw.

    Sub NAME_OF_SHEETS()
    'Dim i As Integer, MyWorkbook As Workbook MySheet As Worksheet' OPTIONAL LINE

    Sheets.Add before: = Sheets (1) 'adds a sheet in 1 position
    'Set MySheet ActiveSheet =' OPTIONAL LINE

    Set MyWorkbook = ActiveWorkbook
    For i = 1 To MyClass.Worksheets.Count
    Range ( "A" & i) = Sheets (i). Name
    Range ( "B" & i) = i 'collar B indicates the position of the leaf' OPTIONAL LINE
    Next
    End Sub

Similar Threads

  1. transfer data Between excel sheets
    By kvirmani in forum MS Office Support
    Replies: 1
    Last Post: 21-04-2011, 05:34 PM
  2. CSS - How to enter style sheets in document?
    By Dexter85 in forum Software Development
    Replies: 4
    Last Post: 23-12-2010, 05:34 AM
  3. Error "document not saved" when saving Excel sheets
    By BansiJ in forum Windows Software
    Replies: 3
    Last Post: 31-07-2010, 03:07 PM
  4. Select a range of sheets in Excel VBA
    By RyanInt in forum Software Development
    Replies: 6
    Last Post: 16-01-2010, 10:47 AM
  5. Comparison of 2 sheets in MS Excel
    By Xeusion in forum Windows Software
    Replies: 3
    Last Post: 21-08-2008, 05:36 PM

Posting Permissions

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