Worksheet List 2 - anyway to shorten and restrict last colum

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
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>
 

Forum statistics

Threads
1,144,113
Messages
5,722,559
Members
422,447
Latest member
srclife

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top