Sub to aquire list of worksheets
 

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

--
aking198
-----------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...fo&userid=1539
View this thread: http://www.excelforum.com/showthread.php?threadid=31434

Sub to aquire list of worksheets
 

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


--
Don Guillett
SalesAid Software

"aking1987" wrote in message
...

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.


--
aking1987
------------------------------------------------------------------------
aking1987's Profile:
http://www.excelforum.com/member.php...o&userid=15393
View this thread: http://www.excelforum.com/showthread...hreadid=314346

Sub to aquire list of worksheets
 

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.


"aking1987" wrote:


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.


--
aking1987
------------------------------------------------------------------------
aking1987's Profile: http://www.excelforum.com/member.php...o&userid=15393
View this thread: http://www.excelforum.com/showthread...hreadid=314346

Sub to aquire list of worksheets
 

This is for WORKBOOKS.

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
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


--
Don Guillett
SalesAid Software

"aking1987" wrote in message
...

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.


--
aking1987
------------------------------------------------------------------------
aking1987's Profile:
http://www.excelforum.com/member.php...o&userid=15393
View this thread:
http://www.excelforum.com/showthread...hreadid=314346

Sub to aquire list of worksheets
 

aking1987 wrote:
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