Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



List the names of sheets in an Excel document

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 19-11-2009
Member
 
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.

Reply With Quote
  #2  
Old 19-11-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
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
Reply With Quote
  #3  
Old 19-11-2009
Member
 
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
Reply With Quote
  #4  
Old 19-11-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "List the names of sheets in an Excel document"
Thread Thread Starter Forum Replies Last Post
transfer data Between excel sheets kvirmani MS Office Support 1 21-04-2011 06:34 PM
CSS - How to enter style sheets in document? Dexter85 Software Development 4 23-12-2010 06:34 AM
Error "document not saved" when saving Excel sheets BansiJ Windows Software 3 31-07-2010 04:07 PM
Select a range of sheets in Excel VBA RyanInt Software Development 6 16-01-2010 11:47 AM
Comparison of 2 sheets in MS Excel Xeusion Windows Software 3 21-08-2008 06:36 PM


All times are GMT +5.5. The time now is 06:42 AM.