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, ""
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


5 comments:

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

    ReplyDelete
    Replies
    1. Hi Sarfraj

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

      Delete
    2. Hi Sarfraj

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

      Delete