Sheet names into an array

[decafme]

Hi guys,

I know this must be possible, but I can't think how to approach it.

I want to be able to allocate the names of each worksheet in a workbook to an vba array, without knowing how many sheets are in the workbook when the code is run, or what the sheet names are.

Is this possible, and how would I go about doing it? Let me know if this is not specific enough, and I'll try to go into more detail.

Thanks

 

[TryingToLearn]

This should work for you:

Sub sheetray()
    Dim SNarray, i
    ReDim SNarray(1 To Sheets.count)
    For i = 1 To Sheets.count
        SNarray(i) = ThisWorkbook.Sheets(i).Name
Debug.Print SNarray(i)
    Next
End Sub

HTH

 

[earlyd]

This might help - it will list the sheets on the active sheet as well as put them in an array...

Sub Macro1()
Dim ShtNames() As String
ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)
For i = 1 To Sheets.Count
ShtNames(i) = Sheets(i).Name
Next i

For i = 1 To Sheets.Count
Cells(i, 1) = ShtNames(i)
Next i
End Sub

 

[decafme]

Brilliant guys, thanks very much.