Copy selected worksheets to new workbook via form 1

[DatabaseDude]

I have a userform with a listbox containing all worksheets in the current workbook.

My goal is to allow users to select worksheets from the list, and for these selected sheets to be copied to a new workbook.

When recording a macro of this process, I find the following:

Sheets(Array("Sheet 1","Sheet 2","Sheet 3")).Select
Sheets(Array("Sheet 1","Sheet 2","Sheet 3")).Copy

Of course, I'd simplify it as so:

Sheets(Array("Sheet 1","Sheet 2","Sheet 3")).Copy

Now ... how do I insert the selected worksheets from the listbox control into this code segment?

Thanks in advance,
Bryant

 

[rmikesmith]

Bryant,

Here is the only way I could get this to work with the Sheets(Array) construct. The following code example could be placed into the Click event of a CommandButton on your Userform. It assumes a ListBox named lstSheets from which the user selects the sheets to copy:

Dim vSheets As Variant
Dim sSelected As String
Dim i As Long

   With lstSheets
     sSelected = ""
     For i = 0 To .ListCount - 1
       If .Selected(i) Then
         sSelected = sSelected & "," & .List(i)
       End If
     Next i
   End With
   
   sSelected = Mid$(sSelected, 2)
   vSheets = Split(sSelected, ",")
   ThisWorkbook.Sheets(vSheets).Copy

Regards,
Mike