Runtime Error 9: Subscript out of Range

[murugavelmsc]

Hi,

I have the "Runtime Error 9: Subscript out of Range" in the following code.

Sub SaveWithoutMacros()
Dim stemp As Variant
i = 1
For Each sht In Sheets
If i = 1 Then
a = sht.Name
If Sheets(a).Visible = True Then
stemp = stemp & """" & sht.Name & """"
End If
i = i + 1
Else
a = sht.Name
If Sheets(a).Visible = True Then
stemp = stemp & ", """ & sht.Name & """"
End If
End If
'msgbox sht.Name
Next sht
msgbox stemp
'Sheet1.Range("A1").Value = stemp
'Sheets(stemp).Select

Sheets(Array(stemp)).Select 'Error in this line
Sheets(Array(stemp)).Copy

End Sub

Please help me.

Regards
Murugavel S

[Bob Phillips]

What does stemp show when you MsgBox it? It looks like it is not an index to me, so it cannot refer to an array item.

[p45cal]

ActiveSheet.Select 'to unselect multiple sheets
For Each sht In Sheets
If sht.Visible Then sht.Select False
Next sht
ActiveWindow.SelectedSheets.Copy

[GTO]

Hi there,

I hope you'll answer XLD's question, as I suspect we both may get to learn a bit :-)

Since I already strained my pea-brain, and was thinking this might be a decent way:

Option Explicit

Sub exa()
Dim aryNames() As Variant
Dim i As Long
Dim wks As Worksheet

ReDim aryNames(1 To ThisWorkbook.Worksheets.Count)

For Each wks In ThisWorkbook.Worksheets
If wks.Visible = xlSheetVisible Then
i = i + 1
aryNames(i) = wks.Name
End If
Next
ReDim Preserve aryNames(1 To i)

ThisWorkbook.Worksheets(aryNames).Copy
End Sub

[Aflatoon]

Passing a comma separated string to the array function creates an array with one element - that whole string - not an array split by the commas. You should either use p45Cal's method or do something akin to this:
Sub SaveWithoutMacros()
Dim stemp As Variant
Dim sht As Object
For Each sht In Sheets
If sht.Visible = xlSheetVisible Then
stemp = stemp & "," & sht.Name
End If
Next sht
MsgBox stemp
stemp = Mid$(stemp, 2)

Sheets(Split(stemp, ",")).Copy

End Sub

[murugavelmsc]

Hi GTO,

Superb
Thanks a lot.

Regards
Murugavel S