Worksheet List 2 - anyway to shorten and restrict last colum

[itr674]

1. Anyway to shorten this?
2. Anyway to make the last column list only sheets that have not already been listed?

Richie helped with this one.

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

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

Sheets(2).Columns("A:H").Clear
For Each ws In ThisWorkbook.Sheets
wsName = Right(ws.Name, 2) 'look at right last 2 letters of name
Select Case wsName
Case "CL"
Sheets(2).Cells(colA, 1).Value = ws.Name
colA = colA + 1
End Select
Next ws

For Each ws In ThisWorkbook.Sheets
wsName = Right(ws.Name, 3) 'look at right last 3 letters of name
Select Case wsName
Case "DTM"
Sheets(2).Cells(colB, 2).Value = ws.Name
colB = colB + 1
End Select
Next ws

For Each ws In ThisWorkbook.Sheets
wsName = Left(ws.Name, 3) 'look at left first 3 letters of name
Select Case wsName
Case "mis"
Sheets(2).Cells(colC, 3).Value = ws.Name
colC = colC + 1
Case Else
Sheets(2).Cells(colD, 4).Value = ws.Name
colD = colD + 1
End Select
Next ws

End Sub</pre>

 

[rikrak]

Hi Em,

Is this want you want:

<pre>
Sub ListSheets()
Dim ws As Worksheet
Dim wsName As String
Dim rowInColA, rowInColB, rowInColC, rowInColD As Double

'start on row 2 to allow for heading in row 1
rowInColA = 2
rowInColB = 2
rowInColC = 2
rowInColD = 2

Sheets(2).Columns("A:H").Clear

For Each ws In ThisWorkbook.Sheets
wsName = Right(ws.Name, 2) 'get last 2 characters of name
If wsName = "CL" Then
Sheets(2).Cells(rowInColA, 1) = ws.Name
rowInColA = rowInColA + 1
Else
wsName = Right(ws.Name, 3) 'get last 3 characters of name
If wsName = "DTM" Then
Sheets(2).Cells(rowInColB, 2) = ws.Name
rowInColB = rowInColB + 1
Else
wsName = Left(ws.Name, 3) 'get first 3 characters of name
If wsName = "mis" Then
Sheets(2).Cells(rowInColC, 3) = ws.Name
rowInColC = rowInColC + 1
Else
Sheets(2).Cells(rowInColD, 4) = ws.Name
rowInColD = rowInColD + 1
End If
End If
End If
Next ws

End Sub

</pre>