Hi Simon,

To make a list of sheets, try:

'================
Sub ListSheets()
Dim WB1 As Workbook, WB2 As Workbook
Dim SH As Worksheet
Dim i As Long

Set WB1 = ActiveWorkbook '<<=== CHANGE
Set WB2 = Workbooks("BbookToList.xls") '<<=== CHANGE

With WB1
Set SH = .Sheets.Add(after:=.Sheets(.Sheets.Count))
End With

For i = 1 To WB2.Sheets.Count
SH.Cells(i, "A").Value = WB2.Sheets(i).Name
Next i

End Sub
'<<================

To show a selectable popup list of sheets in the activeworkbook, try:

'================
Sub ShowSheetList()
'Jim Rech
On Error Resume Next
If ActiveWorkbook.sheets.Count <= 16 Then
Application.CommandBars("Workbook Tabs"). _
ShowPopup 500, 225
Else
Application.CommandBars("Workbook Tabs"). _
Controls("More Sheets...").Execute
End If
On Error GoTo 0
End Sub
'<<================

---
Regards,
Norman



"Simon Chang" wrote in message
...
How to get a list of sheets name from a workbook (by selections, but not
to
open it) so that I could make a selection on a sheet name I want.

Thanks in advance.