CODEWITHSUNDEEP
vbaexcel
user7646859
user7646859

Reputation:

Subscript out of range - runtime error 9

can you please advise why the below code does not select the visible sheets, but ends in a runtime error. This is driving me crazy. Thanks for any help.

Sub SelectSheets1()

    Dim mySheet As Object
    Dim mysheetarray As String

    For Each mySheet In Sheets
        With mySheet
            If .Visible = True And mysheetarray = "" Then
                mysheetarray = "Array(""" & mySheet.Name
            ElseIf .Visible = True Then
                mysheetarray = mysheetarray & """, """ & mySheet.Name
            Else
            End If
        End With
    Next mySheet        

    mysheetarray = mysheetarray & """)"                
    Sheets(mysheetarray).Select

End Sub

Upvotes: 2

Views: 370

Answers (3)

Shai Rado
Shai Rado

Reputation: 33682

You should define Dim mySheet As Object as Worksheet.

Also, you can use an array of Sheet.Names that are visible.

Code

Sub SelectSheets1()

    Dim mySheet As Worksheet
    Dim mysheetarray() As String
    Dim i As Long

    ReDim mysheetarray(Sheets.Count) '< init array to all existing worksheets, will optimize later
    i = 0
    For Each mySheet In Sheets
        If mySheet.Visible = xlSheetVisible Then
            mysheetarray(i) = mySheet.Name
            i = i + 1
        End If
    Next mySheet

    ReDim Preserve mysheetarray(0 To i - 1) '<-- optimize array size    
    Sheets(mysheetarray).Select

End Sub

Upvotes: 2

gembird
gembird

Reputation: 14053

I have tried to explain the Sheets a little, HTH. Note: Sheets property is defined on Workbook and on Application objects, both works and returns the Sheets-Collection.

Option Explicit

Sub SheetsDemo()
    ' All sheets
    Dim allSheets As Sheets
    Set allSheets = ActiveWorkbook.Sheets

    ' Filtered sheets by sheet name
    Dim firstTwoSheets As Sheets
    Set firstTwoSheets = allSheets.Item(Array("Sheet1", "Sheet2"))
    ' or simply: allSheets(Array("Sheet1", "Sheet2"))
    ' Array("Sheet1", "Sheet2") is function which returns Variant with strings

    ' So you simply need an array of sheet names which are visible
    Dim visibleSheetNames As String
    Dim sh As Variant ' Sheet class doesn't exist so we can use Object or Variant
    For Each sh In allSheets
        If sh.Visible Then _
            visibleSheetNames = visibleSheetNames & sh.Name & ","
    Next sh

    If Strings.Len(visibleSheetNames) > 0 Then
        ' We have some visible sheets so filter them out
        visibleSheetNames = Strings.Left(visibleSheetNames, Strings.Len(visibleSheetNames) - 1)
        Dim visibleSheets As Sheets
        Set visibleSheets = allSheets.Item(Strings.Split(visibleSheetNames, ","))

        visibleSheets.Select

    End If
End Sub

Upvotes: 1

Vityata
Vityata

Reputation: 43585

Long story short - you are giving a string (mysheetarray) when it is expecting array. VBA likes to get what it expects.

Long story long - this is the way to select all visible sheets:

Option Explicit

Sub SelectAllVisibleSheets()

    Dim varArray()          As Variant
    Dim lngCounter          As Long

    For lngCounter = 1 To Sheets.Count
        If Sheets(lngCounter).Visible Then
            ReDim Preserve varArray(lngCounter - 1)
            varArray(lngCounter - 1) = lngCounter
        End If
    Next lngCounter

    Sheets(varArray).Select

End Sub

Upvotes: 3

Related Questions