Copying sheets from ListBox of names to a new worksheet
 

I want to copy worksheets from one workbook to another using a listbox
populated by worksheet names. How do I get the names from the listbox
into a statment to do the copying?
Help!

Copying sheets from ListBox of names to a new worksheet
 

I created a userform with a listbox and two commandbuttons:

Option Explicit
Public WhichWkbk As Workbook
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim mySheetNames() As String
Dim iCtr As Long
Dim jCtr As Long

With Me.ListBox1
ReDim mySheetNames(1 To .ListCount)

For iCtr = 1 To .ListCount
If .Selected(iCtr - 1) Then
jCtr = jCtr + 1
mySheetNames(jCtr) = .List(iCtr - 1)
End If
Next iCtr
End With

If jCtr = 0 Then
MsgBox "nothing selected!"
Else
ReDim Preserve mySheetNames(1 To jCtr)
WhichWkbk.Sheets(mySheetNames).Copy _
befo=Workbooks("book3.xls").Worksheets(1)
WhichWkbk.Activate
End If

Unload Me

End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

Set WhichWkbk = ActiveWorkbook

Me.ListBox1.MultiSelect = fmMultiSelectMulti

For iCtr = 1 To WhichWkbk.Sheets.Count
With Sheets(iCtr)
If .Visible = xlSheetVisible Then
Me.ListBox1.AddItem .Name
End If
End With
Next iCtr
End Sub


BlackSox wrote:

I want to copy worksheets from one workbook to another using a listbox
populated by worksheet names. How do I get the names from the listbox
into a statment to do the copying?
Help!

--

Dave Peterson