get all sheet names starting with a specific letter

[KDS14589]

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]

Just add an IF statement seeing if it starts with the letter D:

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]

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]

Hi KDS14589,

Try this:

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

 

[Peter_SSs]

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.

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.

 

[KDS14589]

it worked!

thank you

 

[Peter_SSs]

it worked!

thank you

Glad you got it sorted. Thanks for letting us know.

 

[KDS14589]

Thanks for your help on listing worksheet starting with certain letter.
I worked so it fits my need.

	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]

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?

 

[KDS14589]

I'll try the macro idea
thanks