get all sheet names starting with a specific letter

KDS14589

New Member
Joined
Jan 10, 2019
Messages
29
I have this vba code but it returns ALL of the sheet names. But I want another-one that returns the sheets that start with the letter "D" and list them in column H58 on "switchboard".

Sub WORKSHEETLIST()
Dim ws As Worksheet
Dim Counter As Integer
Counter = 0
For Each ws In ActiveWorkbook.Worksheets
Sheets("switchboard").Range("c58").Offset(Counter, 0).Value = ws.Name
Counter = Counter + 1
Next ws
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,138
Office Version
365
Platform
Windows
Just add an IF statement seeing if it starts with the letter D:
Code:
Sub WORKSHEETLIST()
    Dim ws As Worksheet
    Dim Counter As Integer
    Counter = 0
    For Each ws In ActiveWorkbook.Worksheets
        If Left(ws.Name, 1) = "D" Then
            Sheets("switchboard").Range("H58").Offset(Counter, 0).Value = ws.Name
            Counter = Counter + 1
        End If
    Next ws
End Sub
 

KDS14589

New Member
Joined
Jan 10, 2019
Messages
29
this works but now I've added a new wrinkle. I want all the sheets that match what's in a cell on 'general.switchboard' in cell af56. I tried this code but get a 'debug'.


Sub WorkSheetListD()
Dim ws As Worksheet
Dim Counter As Integer
Counter = 0
For Each ws In ActiveWorkbook.Worksheets
If left(ws.Name, 2) = "Worksheets(Worksheets("general.switchboard").Range("AF56")" then
Sheets("general.switchboard").Range("AG58").Offset(Counter, 0).Value = ws.Name
Sheets("general.switchboard").Range("AD58").Offset(Counter, 0).Value = ws.Index
Sheets("general.switchboard").Range("AA58").Offset(Counter, 0).Value = ws.CodeName
Counter = Counter + 1
End If
Next ws
WorkSheetListC

End Sub
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,435
Hi KDS14589,

Try this:

Code:
Option Explicit
Sub WorkSheetListD()
    
    Dim ws As Worksheet
    Dim Counter As Long
    
    Application.ScreenUpdating = False

    Counter = 0
    
    For Each ws In ActiveWorkbook.Worksheets
        With Sheets("general.switchboard")
            If Left(ws.Name, 2) = .Range("AF56") Then
                .Range("AG58").Offset(Counter, 0).Value = ws.Name
                .Range("AD58").Offset(Counter, 0).Value = ws.Index
                .Range("AA58").Offset(Counter, 0).Value = ws.CodeName
            End If
            Counter = Counter + 1
        End With
    Next ws
    
    WorkSheetListC
    
    Application.ScreenUpdating = True

End Sub
Regards,

Robert
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,755
Office Version
365
Platform
Windows
I tried this code but get a 'debug'.
First issue with your code is that this line has extra text (red) that should be removed.
Rich (BB code):
If left(ws.Name, 2) = "Worksheets(Worksheets("general.switchboard").Range("AF56")" then
Secondly, unsure about whether WorkSheetListC is calling another procedure that we don't know about or whether that also needs to be removed.


BTW, when posting code, please use code tags to preserve the indenations making your code easier to read & debug. My signature block below explains how.
 
Last edited:

KDS14589

New Member
Joined
Jan 10, 2019
Messages
29
Thanks for your help on listing worksheet starting with certain letter.
I worked so it fits my need.
Code:
	Sub WorkSheetListD()
Dim ws As Worksheet
Dim Counter As Integer
Counter = 0
For Each ws In ActiveWorkbook.Worksheets
if Left(ws.Name, 3) = Worksheets("general.switchboard").Range("AQ53") Then
Sheets("general.switchboard").Range("AJ58").Offset(Counter, 0).Value = ws.Name
Sheets("general.switchboard").Range("AV58").Offset(Counter, 0).Value = ws.Index
Sheets("general.switchboard").Range("AS58").Offset(Counter, 0).Value = ws.CodeName
Counter = Counter + 1
End If
Next ws
End Sub
But now I want to add another requirement.
I want that list to be alphabetized. I know I can resort my worksheets alphabetically but I keep them in groups according to the category they're working on, not their ultimate effect.
I'm asking you 'Peter_SSs' because you were so helpful before.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,755
Office Version
365
Platform
Windows
I want that list to be alphabetized.
What list?
- AJ58:AJx
- AJ58:AVx
- A58:AVx
- something else?

In any case, what about trying to record a macro to sort whatever it is that you want sorted and see if you can incorporate that into the existing code?
 

Forum statistics

Threads
1,077,990
Messages
5,337,585
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top