CODEWITHSUNDEEP
excelarraylistvba
HL8
HL8

Reputation: 1419

Excel VBA - Sheets(Arrary("Sheet2","Sheet3")) using a variable for sheet names

I want to code the following

Sheets(Arrary("Sheet2","Sheet3")).Select, by creating a variable for the sheet names to replace the "Sheet2","Sheet3".

Sub SelectMultipleSheets()

  Dim sSheets as String
  Dim intSheet As Integer

    sSheets = ""

    For intSheet = 1 To Sheets.count

        If Sheets(intSheet).Name <> "Sheet1" And intSheet <= Sheets.count Then

            Sheets(intSheet).Cells.Hyperlinks.Delete 'deleting hyperlinks

            sSheets = sSheets & Chr(34) & Sheets(intSheet).Name & Chr(34) & ","

        End If

    Next

    sSheets = Left(sSheets, Len(sSheets) - 1)
    Sheets(Array(sSheets)).Select

End Sub

I get an error message "Subscript not in range. How do I fix this? Thanks

Upvotes: 0

Views: 10695

Answers (2)

Steve Homer
Steve Homer

Reputation: 3932

When you build up the array list parameter in this line

sSheets = sSheets & Chr(34) & Sheets(intSheet).Name & Chr(34) & ","

you're actually creating a single comma delimited string variable and Excel has no way of know that you actually mean a list of comma delimited strings.

You can get around it by creating the array directly, like this.

Option Explicit

Sub SelectMultipleSheets()
    Dim intSheet As Integer
    Dim arSheets() As String
    Dim intArrayIndex As Integer

    intArrayIndex = 0

    For intSheet = 1 To Sheets.Count

        If Sheets(intSheet).Name <> "Sheet1" Then

            Sheets(intSheet).Cells.Hyperlinks.Delete 'deleting hyperlinks

            ReDim Preserve arSheets(intArrayIndex)
            arSheets(intArrayIndex) = Sheets(intSheet).Name
            intArrayIndex = intArrayIndex + 1
        End If
    Next

    Sheets(arSheets).Select

End Sub

Upvotes: 2

Doug Glancy
Doug Glancy

Reputation: 27488

It can't be done that way, even though it looks like it should work. Instead, the code below takes advantage of Select's Replace argument to add to the selection in the loop. The boolNoSelectionYet variable ensures that it doesn't add to the selection that existed before the loop starts, e.g., if Sheet1 was selected when the routine starts, you don't want it to stay selected.

Sub SelectMultipleSheets()
Dim intSheet As Integer
Dim boolNoSelectionYet As Boolean

boolNoSelectionYet = True
For intSheet = 1 To Sheets.Count
    If Sheets(intSheet).Name <> "Sheet1" Then
        Sheets(intSheet).Cells.Hyperlinks.Delete
        Sheets(intSheet).Select (boolNoSelectionYet)
        boolNoSelectionYet = False
    End If
Next
End Sub

Note that I removed the second part of your If statement as your For Next loop ensures that intSheet will never be more than the count of sheets.

Upvotes: 0

Related Questions