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.

excel copy data to closed workbook

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, ""
Exit Sub
End If

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


  1. I need this template with more columns like 35 columns.(More Details about staff)Please update the template.

    1. Hi Sarfraj

      Did you get you get updated template as per you're requirements

    2. Hi Sarfraj

      Did you get you get updated template as per you're requirements