Create List of Worksheet Names

[Vamphyri]

How would I create a list, later used to populate a DD Box, of all worksheet names in a workbook?

 

[Remou]

Hi
Could you use something like this:

With ActiveWorkbook
For i = 1 To .Worksheets.Count
    Debug.Print .Worksheets(i).Name
Next i
End With

I left it at debug.print because I am not sure where you want to store the results, if at all.

 

[Vamphyri]

Remou,

I would like to store it in a new worksheet, if possible.

Any ideas.

Can you tell I'm really new at this, or is it just my underwear?

 

[SkipVought]

Hi,

dim ws as worksheet, lRow as long
with Sheets("MyWorksheetListSheet")
  .clearcontents
  .[A1] = "SheetList"
  lRow = 2
  for each ws in worksheets
     if ws.name <> .name then
       .cells(lRow, 1).value = ws.name
       lRow = lRow + 1
     end if
  next
end with

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[macropod]

Or, more simply, to put all sheet names into column A of the first worksheet:

Sub GetSheetNames()
For i = 1 To Sheets.Count
Sheets(1).Range("A" & i).Value = Sheets(i).Name
Next i
End Sub

If you don't want to list the first worksheet, change:
For i = 1 To Sheets.Count
to
For i = 2 To Sheets.Count

You can also tweak the starting address by modifying the line:
Sheets(1).Range("A" & i).Value = Sheets(i).Name
For example, changing 'Range("A" & i)' to 'Range("A" & i+1)' will offset the output one row down.

Cheers

 

[Vamphyri]

Skip,

I get an:
"Invalid Outside Procedure" error.
What am I doing wrong?

 

[PHV]

"Invalid Outside Procedure" error
Seems that your code isn't inside a Sub or a Function ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244