Load certain sheet names into array 1

[davedave24]

Hi. I would like to load certain sheet names into an array. They will all start the same string; let's just say "sheet" (sheet1, sheet2, sheet3 and so on). I would need only those beginning with that string in the array.

Thanks

 

[PHV]

What have you tried so far and where in your code are you stuck ?
Hint: have a look at the ActiveWorkbook.Worksheets collection and the Left function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davedave24]

Hi, this is what I was working with

    Dim SheetNames() As String
    Dim i As Integer
    
    ReDim SheetNames(Sheets.Count - 1)
    For i = 0 To Sheets.Count - 1
        [COLOR=red]If Mid(SheetNames(i), 1, 5) = "Sheet" Then[/color]
            SheetNames(i) = Sheets(i + 1).Name
            'MsgBox SheetNames(i)                           
        End If
        
    Next i

 

[davedave24]

Actually, this seems to work:

    Dim SheetNames() As String
    Dim i As Integer
    Dim ws As Worksheet
    
    ReDim SheetNames(Sheets.Count - 1)
    For Each ws In ActiveWorkbook.Worksheets
        If Mid(ws.Name, 1, 5) = "Sheet" Then            
            SheetNames(i) = ws.Name            
            [COLOR=green]'ListBox1.AddItem ws.Name[/color]
            MsgBox SheetNames(i)            
        End If
    Next

 

[davedave24]

EDIT: Actually that isn't working so well. It won't let me access the array anywhere outside of that For loop; it just returns blank results if I do Msgbox Sheetnames(1), for example

 

[Gavona]

i is always zero
Add i = i+1 after the MsgBox statement

Gavin

 

[davedave24]

Oh my days. Thanks guys.

 

[davedave24]

For future reference, my code there wasn't working properly at all. the UBound of the array was the total number of the sheets, not how many were selected on their name. I found some code here that does it properly:

Sub ListSheets()
    Dim SheetNames() as String
    Dim i As Integer, j As Integer
     
    ReDim SheetNames(ThisWorkbook.Sheets.Count)
    For i = 1 To Sheets.Count
        If Mid(Sheets(i).Name, 1, 5) = "Sheet" Then
            SheetNames(j) = Sheets(i).Name
            ListBox1.AddItem Sheets(i).Name
            j = j + 1
        End If
    Next i
    
    ReDim Preserve SheetNames(j)
        
    MsgBox UBound(SheetNames)
End Sub

 

[Andrzejek]

Since you did all this work, consider this:

Option Explicit
Dim SheetNames() As String

Sub ListSheets()
Dim ws As Worksheet
Dim i As Integer

For Each ws In ActiveWorkbook.Sheets
    If Left(ws.Name, 5) = "Sheet" Then
        ReDim Preserve SheetNames(i)
        SheetNames(i) = ws.Name
        i = i + 1
    End If
Next

For i = LBound(SheetNames) To UBound(SheetNames)
    MsgBox SheetNames(i)
Next i

End Sub

Have fun.

---- Andy