how do i list the name off all my worksheets on a sheet

[Guest]

i want to list all the worksheets in a particular workbook on a worksheet
that i have named Master. Can someone hlep me on this please ?

 

[Biff]

See reply in .Misc

Biff

 

[Guest]

sorry Biff. I'm new to this, where is .Misc??

See reply in .Misc

Biff

 

[Biff]

Hi!

I see that you're using the MS web interface.

In that interface in the EXCEL group it's called GENERAL QUESTIONS.

Biff

 

[Max]

Here's one response which was posted to your other post in .misc a little
earlier (Kindly refrain from multi-posting)
-----------------------

is there a way of listing all my worksheets
on a master worksheet?

Another option, try the sub below:

Steps
--------
Press Alt+F11 to go to VBE
Click Insert > Module
Copy > paste everything within the dotted lines below
into the whitespace on the right

-------begin vba-----
Sub SheetNames()
'Peo Sjoblom in .worksheet.functions Jul '02
Dim wkSht As Worksheet
Range("A1").Select
For Each wkSht In Worksheets
Selection = wkSht.Name
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next wkSht
End Sub
-------endvba------

Press Alt+Q to get back to Excel

In a *new* sheet, press Alt+F8
Select "SheetNames" > Run

The sheetnames will be listed in A1 down, in this sequence:

1st sheet (leftmost) will be listed in A1,
2nd sheet in A2, and so on

Hidden sheets will also be listed
and will appear after the last (rightmost) sheet

 

[Arvi Laanemets]

Hi

You can try a couple of UDF's

Public Function CurrentTab(Optional MyTime As Date)
' returns the name of current sheet, use DATE() or NOW() as optional
parameter to make the function to recalculate automatically
CurrentTab = Range("A1").Worksheet.Name
End Function

Public Function TabI(TabIndex As Integer, Optional MyTime As Date) As String
' returns the name of n-th sheet in workbook, use TODAY() or NOW() as
optional parameter to make the function to recalculate automatically
TabI = Sheets(TabIndex).Name
End Function


An example how to use the function TABI to return the list of worksheets
=IF(ISERROR(TABI(ROW(B1,TODAY()))),"",TABI(ROW(B1)))
, and copy the formula down for some number of rows

 

[Skin]

Hi One Way Open a new module in VBA Type this macro or copy and paste into
module. Close and return to excel
Caveat: The workbook must be the active workbook.
Select the sheet you want in the active workbook, the sheet names, select a
cell where you want the first sheets name to appear, go to Tools > Macros >
select NameSheets > run

Sub NameSheets()
Dim SheetNames() As String
Dim i As Integer
Dim SheetCount As Integer
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = SheetNames(i)
Next i
End Sub

Paul, any probs repost