Worksheet - need code to list all WS and organize them??

[itr674]

Need code to list all the worksheets in a book on a worksheet named "WS List" and need sheets prefixed with "mis" to be listed under "Miscellenous" column (A), and prefixed sheet names of "inp" to be listed under "Inspection" column (C).
This message was edited by em on 2002-10-18 10:58

 

[Joe Was]

These may get you started. JSW

Sub AllSheets1atATime()
'Get a sheet name.
For Each ws In Worksheets
'Display the found sheet name.
MsgBox ws.Name
Next ws
'Go back and get the next sheet name.
End Sub

Sub AllSheets()
Dim myList As String
'Get all sheets name.
For Each ws In Worksheets
'Store one sheet name after another!
myList = myList + ws.Name
'Add a comma and space between each stored name.
myList = myList + ", "
Next ws
'Display all the found sheet names.
MsgBox myList
End Sub

Sub BuildSListDD()
Dim myList As String
For Each ws In Worksheets
myList = myList + ws.Name
myList = myList + ","
Next ws
With Range("B5").Validation
.Add xlValidateList, xlValidAlertStop, xlBetween, myList
.InCellDropdown = True
End With
Worksheets("Sheet1").Range("B5").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

 

[Richie(UK)]

Hi,

How about this :

<pre>
Sub ListSheets()
Dim ws As Worksheet, wsName As String
Dim colA As Integer, colC As Integer

colA = 2
colC = 2 'start on row2 to allow for heading in row1

For Each ws In ThisWorkbook.Sheets
wsName = Mid(ws.Name, 1, 3) 'look at first 3 letters of name
Select Case wsName
Case "mis"
Sheets(1).Cells(colA, 1).Value = wsName
colA = colA + 1
Case "inp"
Sheets(1).Cells(colC, 3).Value = wsName
colC = colC + 1
Case Else
MsgBox "This sheet does not match the search criteria:" _
& vbLf & ws.Name
End Select
Next ws

End Sub

</pre>

HTH

By the way, nothing wrong with your routines Joe :wink: its just that I started looking at this before leaving work and now I'm back at home.

 

[Joe Was]

I like the way you use "Case" to handle the criteria in the problem. JSW

 

[itr674]

What sheet is the code suppose to put the sheet names on?

I inserted a sheet and then put the code in that sheet but when I run it it looks like it is trying to put data on the first sheet in the workbood which is called Menus?

It also appears to be listing the prefix only and not the entire sheet name?

 

[itr674]

Richie--OK I figured out what sheet it is suppose to go on, but it still is not listing the sheet names?

 

[Richie(UK)]

Hi em,

Sorry about that - change wsName in the 'Case' lines to ws.Name. The former is the first three letters of the name used for testing the latter is the full worksheet name.

HTH

 

[itr674]

Richie - perfect - thanks ...

 

[itr674]

Richie - how could I modify the code to list all the sheets that don't have a prefix. I can figure out how to get it in another column, but can't figure out the Case statement to get sheet without prefix??