Sub to aquire list of worksheets

[aking1987]

Title says it all!

Would like a sub to aquire the list of worksheet names (exluding shee
name "EG1)

Then paste the names within a specified column (IE: I)

Thank you

 

[Don Guillett]

Here is a macro to make your list
Sub GetFileList()
Dim iCtr As Integer
With Application.FileSearch
.NewSearch
.LookIn = "c:\aa"
.SearchSubFolders = True
.Filename = ".xls"
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Next iCtr
End If
End With
End Sub

 

[Guest]

Try:

Sub xx()
Range("A1").Select
For Each Sheet In ActiveWorkbook.Sheets
Select Case Sheet.Name
Case "EG1"
'Ignore
Else
ActiveCell.Value = Sheet.Name
Cells(Cells.Row + 1, Cells.Column).Activate
End Select
Next
End Sub

You need to:

1. Change the location where the names should appear: I've used A1
2. Use ActiveWorkbook.Sheets if you want to include chart sheets otherwise
use ActiveWorkBook.Worksheets.

 

[Don Guillett]

This is for WORKBOOKS.

--
Don Guillett
SalesAid Software
(e-mail address removed)

Here is a macro to make your list
Sub GetFileList()
Dim iCtr As Integer
With Application.FileSearch
.NewSearch
.LookIn = "c:\aa"
.SearchSubFolders = True
.Filename = ".xls"
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Next iCtr
End If
End With
End Sub

 

[Alan Beban]

Title says it all!

Would like a sub to aquire the list of worksheet names (exluding sheet
name "EG1)

Then paste the names within a specified column (IE: I)

Thank you.

You can check the reference to Microsoft Scripting Runtime, and use

Dim x As Dictionary, i As Integer
Set x = New Dictionary
For i = 1 To Worksheets.Count
x.Add Item:=Worksheets(i).Name, key:=CStr(Worksheets(i).Name)
Next
x.Remove ("EG1")
Worksheets("MasterWorksheet").Range("I1:I" & _
Worksheets.Count - 1).Value = Application.Transpose(x.Items)

Alan Beban