get all sheet names starting with a specific letter

KDS14589

Board Regular
Joined
Jan 10, 2019
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top