Build up an array of workbooks

[Giordano Bruno]

I have a userform with tick boxes (chk1, chk2 and chk3) for three named worksheets. I wish to export as a group whichever worksheets are chosen. What is the best way to build up an array or worksheets based upon the boxes that have been checked? Any help will be most gratefully received.

 

[Norie]

How are the checkboxes named?

 

[LockeGarmin]

For a dynamically growing list of items the best built-in class would be a Collection. The code below will demonstrate. To get this to work, create a new workbook with three worksheets, and create a userform with three checkboxes and a button (don't change any of the default names) and put this code into the userform code behind (Double click the userform and press F7). Then just launch the userform straight from the VB Editor (double click the userform and press F5).

I would recommend storing the worksheets by code name instead of by their regular name but either way it would be the same worksheet object getting stored in the collection.

Private Sub CommandButton1_Click()

Dim WS As Worksheet

  For Each WS In GetSelectedWorksheets
    MsgBox WS.Name
  Next WS
  
End Sub

Function GetSelectedWorksheets() As Collection

Dim SelectedWorksheets As Collection

  Set SelectedWorksheets = New Collection
  
  'Worksheets by CodeName
  If CheckBox1.Value Then SelectedWorksheets.Add Sheet1
  If CheckBox2.Value Then SelectedWorksheets.Add Sheet2
  If CheckBox3.Value Then SelectedWorksheets.Add Sheet3

  'Worksheets by Name
  'If CheckBox1.Value Then SelectedWorksheets.Add Worksheets("Sheet1")
  'If CheckBox2.Value Then SelectedWorksheets.Add Worksheets("Sheet2")
  'If CheckBox3.Value Then SelectedWorksheets.Add Worksheets("Sheet3")

  Set GetSelectedWorksheets = SelectedWorksheets

End Function

 

[Giordano Bruno]

Hi Norrie, Thanks for your interest. The Check boxes are named chk1, chk2 and chk3

Hi LockeGarmin, Thanks for your contribution. Can you tell me how this code can be used to group the selected worksheets so that I can export them together to a new workbook?

 

[Norie]

Use an array.

Dim arrShts()
Dim I As Long
Dim cnt As Long

    For I = 1 To 3
        If Me.Controls("chk" & I).Value = True Then
            ReDim Preserve arrShts(cnt)
            arrShts(cnt) = Me.Controls("chk" & I).Caption ' assumes the captions of the checkboxes are the sheet names
            cnt = cnt + 1
        End If
    Next I

    If cnt>0 Then
        Sheets(arrShts).Copy
    End If

 

[LockeGarmin]

You can use this function to copy a collection of worksheet objects to a new workbook.

Function CopyWorksheetsToNewWorkbook(WorksheetCollection As Collection) As Workbook

Dim WS As Worksheet
Dim LastWs As Variant

  LastWs = Null
  For Each WS In WorksheetCollection
    Call WS.Copy(After:=LastWs)
    Set LastWs = ActiveSheet
  Next WS

  Set CopyWorksheetsToNewWorkbook = ActiveWorkbook

End Function

Then the button in my example code from before can use both functions to make the new workbook.

Private Sub CommandButton1_Click()
  CopyWorksheetsToNewWorkbook GetSelectedWorksheets
End Sub

 

[Giordano Bruno]

Many thanks for that both of you.
Norrie. That's perfect and a very neat loop. Many thanks for the code which is much appreciated.
LockeGarmin. This looks like an interesting alternative approach, but when I tried it GetSelectedWorksheets was highlighted and I got a message ByRef argument type mismatch. I can't see what I'm doing what I'm doing wrong.