need some VBA code!

[Ron99]

Hi Guys,

I have a spreadsheet where I have 40 tabs, I would like a code which can extract all the names of this tab and put it in one sheet. Is this possible ?

Regards,
Ron.....

 

[JamesW]

Hi,

Very simple version (I'm sure there'll be other ways):

Sub test()
    i = 1
    For Each sht In Sheets
        Cells(i, 1).Value = sht.Name
        i = i + 1
    Next sht
End Sub

 

[GDH]

Hi Ron99

If you download the ASAP utilities add-inn, there is an option to create an index page of all sheets in the workbook (in the sheets menu). An additional benefit is that each of these are hyperlinked to the sheet.

Follow the link for ASAP http://www.mrexcel.com/forum/showthread.php?t=126629

GDH

 

[eric cannelora]

Here's another solution (perhaps it's needlesly complex...)

Sub ListWkshts()
Dim shts() As String
Dim sheetCount As Integer, i As Integer
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Name = "SheetNames"
sheetCount = ActiveWorkbook.Sheets.Count
i = 1
ReDim shts(1 To sheetCount)
For Each sheet In ActiveWorkbook.Sheets
    shts(i) = sheet.Name
    i = i + 1
Next sheet
ws.Range("a1").Resize(sheetCount) = _
    Application.WorksheetFunction.Transpose(shts)
End Sub

it will create a sheet called "SheetNames" and add the names there

 

[Ron99]

thank you....it works fine!..have a great day

 

[Misca]

I'm not sure what do you mean with "names of this tab" but here's a couple of different things:

Sub AddSheetsWithNames()
'Adds new sheets and names them from the selected cells

Dim Rng As Range
Dim c As Range
Dim ws As Worksheet
Dim i As Integer
Dim WSName As String

Set Rng = Selection

For Each c In Rng
    If c.Value <> "" Then
        WSName = c.Value
        Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
        
        If WorksheetExists(WSName) = True Then 'The name already exists (needs the following function to work)
            i = 1
            Do While WorksheetExists(WSName & i) = True
                i = i + 1
            Loop
            WSName = WSName & i
        End If
        
        ws.Name = WSName
    End If
    
Next c

'Goes back to the original sheet:
Rng.Parent.Activate

End Sub

Function WorksheetExists(ByVal WorksheetName As String) As Boolean

On Error Resume Next
    WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0

End Function


Sub TabNames()
'Lists the sheet names under the active cell

Dim i As Integer

For i = 1 To Worksheets.Count
    ActiveCell.Offset(i).Value = Sheets(i).Name
Next i

End Sub

Sub NamedRangesOnTab()
'Lists the named ranges that refer to this sheet from the activecell on

Dim N As Name
Dim i As Integer

For Each N In ActiveWorkbook.Names
    If InStr(N, ActiveSheet.Name) > 0 Then
        ActiveCell.Offset(i).Value = N.Name
        ActiveCell.Offset(i, 1).Value = Replace(N.RefersTo, "=", "")
        i = i + 1
    End If
Next N
End Sub

 

[Rick Rothstein]

You might find this an interesting alternative to what you asked for. See my response (Rick Rothstein) in this thread...

http://answers.microsoft.com/en-us/...cel-2003/b15fb4a5-3eac-471c-b3a7-9b7418de641f