Excel Vba :Copy The Listbox Items Into Closed Workbook

VBA Copy Data To The Selected Sheet Of The Closed Workbook

        We have used address book template as sample in this tutorial.

List of the listbox or listbox item can be copied into other closed workbook with a button.

Is pressed "Copy Listbox" button , sheets of the closed workbook are listed in the drop-down list. Codes to list sheets of the closed workbook on combobox control :

Sub add_sheets()
Dim m As Byte
Workbooks.Open (ThisWorkbook.Path & "\Database.xls")
        For m = 1 To Sheets.Count
        UserForm2.ComboBox1.AddItem Sheets(m).Name
         Next m
    ActiveWorkbook.Close True
 UserForm2.ComboBox1.Enabled = True
End Sub

For convenience, we put both workbooks in the same folder. Our workbooks ;
📗 address_book_listbox_copy.xls  (workbook with userform)
📗 Database.xls  (the workbook from which the copying was made)


✔️ So that ,user can copy the contents of the listbox to the selected sheet.

All of the codes we used for copying (Copy Listbox button on userform) :

Private Sub CommandButton10_Click()
Application.ScreenUpdating = False
If ListBox1.ListCount = 0 Then
MsgBox "No items that will be copied.", vbCritical, ""
Exit Sub
End If
Call add_sheets

If ComboBox1.Value = "" Then
MsgBox "Please Choose A WorkSheet From Drop-Down List ", vbInformation, ""
ComboBox1.SetFocus
Exit Sub
End If

Workbooks.Open (ThisWorkbook.Path & "\Database.xls")
Sheets(ComboBox1.Value).UsedRange.Cells.Clear
Sheets(ComboBox1.Value).Range("A2:L" & ListBox1.ListCount + 1) = ListBox1.List
Sheets(ComboBox1.Value).Columns.AutoFit
ActiveWorkbook.Close True
MsgBox "The Listbox Records Were Copied.", vbInformation, ""
ComboBox1.Clear
ComboBox1.Enabled = False
Application.ScreenUpdating = True
End Sub