Dynamically Add Sheets to Sheets Array

[wsnyder]

Hi all,

I am trying to dynamically add sheets to a sheets array if some condition is met.
How can I accomplish this?

I thought maybe I could build up a string of sheet names, but in the code below I am getting

Subscript out of range error

when trying to pass the string of sheet names to the Sheets Array

Thanks,
-w

Option Explicit
Sub SheetsArray()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsArr As Sheets
    Dim strSheets As String
    Dim i As Integer
 
    'Initialize
        Set wb = ThisWorkbook
        i = wb.Worksheets.Count
        strSheets = vbNullString
 
 
    With wb
        If i > 1 Then
            For Each ws In .Worksheets
                strSheets = strSheets & """" & ws.Name & """" & ","
            Next ws
            strSheets = Left(strSheets, Len(strSheets) - 1)
            Debug.Print strSheets
            Set wsArr = .Sheets(Array(strSheets))
        Else
            MsgBox "Only 1 worksheet in the workbook"
        End If
    End With
End Sub

 

[DanteAmor]

Hi @wsnyder

You are not explaining why you need all the sheets in an array.

I give you an example:

Sub SheetsArray()
  Dim sh As Worksheet
  Dim wks()
  Dim n As Long
 
  If Sheets.Count > 1 Then
    For Each sh In Sheets
      ReDim Preserve wks(n)
      wks(n) = sh.Name
      n = n + 1
    Next
    'What do you want to do with the sheet array?
    'For example, copy all sheets:
    Sheets(wks).Copy
  Else
    MsgBox "Only 1 worksheet in the workbook"
  End If
End Sub

But if you want to select all the sheets if they are more than one sheet, just:

Sub SheetsArray_v2()
  If Sheets.Count > 1 Then
    Sheets.Copy
  Else
    MsgBox "Only 1 worksheet in the workbook"
  End If
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------