"Run-time error '9' Subscript out of Range" when trying to copy Sheet name to an array element

[bdrumr56]

Hello all:

First post. I'm using VBA in Excel 2007. I have been trying to code a subroutine to find the last sheet and the sheet names of the tabs in a workbook. Then I'm attempting to place those sheet names in an array.

I suspect that I'm not using the right class of object or the right method, but here is the code:

Sub PopulateArrayWithSheetNames()
    Dim LastAddedSheet As Worksheet
    Dim oneSheet As Worksheet
    Dim My_sheetNames() As String
    Dim i As Long
    Dim rngThisWorkbook As Range
    Dim wrkbkName As String
    
    
    i = 0
    wrkbkName = "TEST901GOODA.xlsm"
    Workbooks(wrkbkName).Activate
    
    With ThisWorkbook
        Set LastAddedSheet = .Sheets(1)
        For Each oneSheet In .Sheets
            i = i + 1
            If Val(Mid(oneSheet.CodeName, 6)) > Val(Mid(LastAddedSheet.CodeName, 6)) Then
                Set LastAddedSheet = oneSheet
            End If
            
            'Copy OneSheet(SheetName) to an Array Value(i)
            MsgBox "My sheet is named: " & oneSheet.Name
            MsgBox "My sheet's Codename is: " & oneSheet.CodeName
            'My_sheetNames((i)) = oneSheet.CodeName
            My_sheetNames(1) = oneSheet.Name
        
        'i = i + 1
        Next oneSheet
    End With
    MsgBox LastAddedSheet.Name & " was last added."
End Sub

Can someone give me a hand?

Thanks in advance!

Bdrumr56

 

[shg]

Welcome to the board.

Here's one way. It assumes that the codenames of the worksheets have not been changed from their default Sheet# names.

Sub PopulateArrayWithSheetNames()
    Dim iSht        As Long
    Dim asSht()     As String
    Dim iNum        As Long
    Dim iNumMax     As Long
    Dim wks         As Worksheet
 
    With ThisWorkbook
        ReDim Preserve asSht(1 To .Worksheets.Count, 1 To 2)
        For iSht = 1 To .Worksheets.Count
            asSht(iSht, 1) = .Worksheets(iSht).Name
            asSht(iSht, 2) = .Worksheets(iSht).CodeName
            iNum = Mid(asSht(iSht, 2), 6)
            If iNum > iNumMax Then
                iNumMax = iNum
                Set wks = .Worksheets(iSht)
            End If
        Next iSht
        MsgBox wks.CodeName & "(" & wks.Name & ") was last added (maybe)."
    End With
End Sub

 

[bdrumr56]

Thanks, shg! Tested it and it works fine.

Some of the CodeNames have changed, but I can access those data files and change the CodeNames back.

This looks like what I'm needing.

Now is there a way to use those saved Names or Codenames to activate the particular sheet and pull a range of data from it to copy to a sheet in another workbook?

I think I'll be able to figure that part out on my own, but any thoughts would be appreciated. After all, I realize that is carrying things a lot farther.

Thanks, again. The "Subscript out of range" error was driving me crazy!

bdrumr56

 

[shg]

You're welcome, good luck.