Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List of worksheets | New Users to Excel | |||
Get a list of worksheets from a WB | Excel Discussion (Misc queries) | |||
new worksheets based on list | Excel Programming | |||
Get a List of Worksheets | Excel Programming | |||
VBA list all worksheets | Excel Programming |