Search code examples
arraysexcelvbaworksheet

Adding Sheet Names to Array in Excel VBA

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
Solution

  • 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.