Move worksheets selected in listbox to another book

[JimHol]

Hello All,

I have a form that contains a list box populated with all of the worksheets in the current workbook. I need to be able to move the selected worksheets form the current workbook to another while keeping in mind the following:

The list box may have one or multiple sheets selected.

The destination workbook may or may not be opened.

Thanks for the help,
Jim

 

[mffap]

Search in this forum or on google how to copy sheets from workbook to another. One good example is here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=359

Although, the first thing you should do is to parse the listbox to an array (i.e. here http://bytes.com/topic/visual-basic/answers/825599-multiselect-listbox-array)

-- Google finds it for you.

 

[Jon von der Heyden]

Are we talking ActiveX listbox on a userform here, or...?

 

[JimHol]

On a userform the procedure is I would select the sheet or sheets then press a button to run the macro.

 

[Jon von der Heyden]

Here goes...

I generally have a reusable function for returning a workbook. If the wkb isn't open then it opens it and sets reference to it. If it is already open then all it does is set reference to it. Stick this in a standard module:

Public Function ReferenceWorkbook(ByVal strFullName As String) As Workbook
    Dim strName As String
    Dim wkb As Workbook

    strName = Dir$(PathName:=strFullName, Attributes:=vbNormal)
    
    If Len(strName) = 0 Then Exit Function
    
    On Error Resume Next
        Set wkb = Workbooks(strName)
    On Error GoTo 0
    
    If Not wkb Is Nothing Then
        If LCase$(wkb.FullName) = LCase$(strFullName) Then
            Set ReferenceWorkbook = wkb
            Exit Function
        End If
    End If
    
    Set ReferenceWorkbook = Workbooks.Open(Filename:=strFullName, UpdateLinks:=False)
End Function

Next, in your userform class, you could do something like this:

Private Sub CommandButton1_Click()
    Dim wkb As Workbook
    Dim strPath As String: strPath = "C:\Users\Jon von der Heyden\Desktop\Desktop Files\Test.xlsm"
    Dim lngListItem As Long, lngSelected As Long
    Dim varSheets() As String
    Dim blnMoveSheets As Boolean: blnMoveSheets = False
    
    For lngListItem = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(lngListItem) Then
            blnMoveSheets = True
            lngSelected = lngSelected + 1
            ReDim Preserve varSheets(lngSelected - 1)
            varSheets(lngSelected - 1) = Me.ListBox1.List(lngListItem)
        End If
    Next lngListItem
    
    If blnMoveSheets Then
        Set wkb = ReferenceWorkbook(strPath)
        Call ThisWorkbook.Sheets(varSheets).Copy(Before:=wkb.Sheets(1))
    End If
End Sub

Assumes your listbox is called ListBox1 and that CommandButton1 click event is used to trigger this.

You should probably add some extra handling, e.g. such as if nothing is selected in the listbox or if the sheet doesn't exit.

Also, at the moment this is using Copy method but you can just replace this with Move.

 

[JimHol]

Jon,

Not sure what is going on but your code works great when I run it in the workbook containing the macros but when I call the macro from another book through a toolbar I get a subscript out of range on this line:

Call ThisWorkbook.Sheets(varSheets).Copy(Before:=wkb.Sheets(1))

Any ideas?

Thanks,
Jim

 

[Jon von der Heyden]

ThisWorkbook will always refer to the workbook that contains the project. Perhaps you want ActiveWorkbook

 

[JimHol]

Works great Jon! Dont know why I didnt see that.

Thanks,
Jim