Adding Sheet Names to Array in Excel VBA

Question

I am trying to add sheet names into an array in Excel VBA using the code below. It is only picking up one value (always the last worksheet name). For example, if I have 2 sheets: List1 and List2, it only picks up List2 and shows a blank value for the first sheet. If I add 4, it only shows the 4th, and so on. I'm not sure why I'm getting blank values.

Dim curSheet As Worksheet
Dim ArraySheets() As String
Dim x As Variant

For Each curSheet In ActiveWorkbook.Worksheets

    If curSheet.Name Like "*List*" Then

        ReDim ArraySheets(x)

        ArraySheets(x) = curSheet.Name

        x = x + 1

    End If

Next curSheet

Replace ReDim ArraySheets(x) with ReDim Preserve ArraySheets(x). — omegastripes
– omegastripes, Commented May 6, 2015 at 21:31

Soulfire · Accepted Answer · 2015-05-06 21:37:55Z

7

You should change ReDim ArraySheets(x) to ReDim Preserve ArraySheets(x)

When you use just ReDim the contents of the array are not kept, which is why you only get the final sheet name. Using ReDim Preserve re-sizes the array while keeping the contents.

edited May 6, 2015 at 21:37

answered May 6, 2015 at 21:31

Soulfire

4,3161 gold badge25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NDubonix Over a year ago

Is there a way to put the array definition (ReDim Preserve ArraySheets(x)) outside of the For loop? When I place it outside of the loop, I get a "Subscript out of range" error.

brettdj · Accepted Answer · 2015-05-07 08:08:15Z

Without loops

Sub GetNAmes()
Dim strIn As String
Dim X

strIn = Application.InputBox("Search string", "Enter string to find", "*List*", , , , , 2)
If strIn = "False" Then Exit Sub

ActiveWorkbook.Names.Add "shtNames", "=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND(""]"",GET.WORKBOOK(1)))"
X = Filter([index(shtNames,)], strIn, True, 1)

Select Case UBound(X)
    Case Is > 0
        strIn = Application.InputBox(Join(X, Chr(10)), "Multiple matches found - type position to select", , , , , 1)
        If strIn = "False" Then Exit Sub
        On Error Resume Next
        Sheets(CStr(X(strIn))).Activate
        On Error GoTo 0
    Case 0
        Sheets(X(0)).Activate
    Case Else
        MsgBox "No match"
End Select

End Sub

Collectives™ on Stack Overflow

Adding Sheet Names to Array in Excel VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related