Need a solution for copy the selected items in listbox to a newworkbook

Baski007 · Apr 30th 2020

Hi all,

I tried with the below command and i can't paste the selected values in listbox to a new workbook. with that command i can open the workbook but the selected values are not pasting in the new workbook. can suggest any solution.

Code

Private Sub CommandButton2_Click()
Dim wkb As Workbook
'Adding New Workbook
Set wkb = Workbooks.Add
Dim i As Integer
With wkb
.Worksheets("Sheet1").Activate
End With
r = 1
For i = 1 To ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
With Sheet1
.Cells(r, 1).Value = Me.ListBox1.List(i, 0)
.Cells(r, 2).Value = Me.ListBox1.List(i, 1)
.Cells(r, 3).Value = Me.ListBox1.List(i, 2)
r = r + 1
End With
End If
Next
End Sub

Display More

dangelor · Apr 30th 2020

Try changing line 13 to this:

Code

With wkb.Worksheets("Sheet1")

royUK · Apr 30th 2020

If you activate the sheet as your code does, unnecessarily because it will be the active sheet by default .

Code

Option Explicit
Private Sub CommandButton2_Click()
Dim wkb As Workbook
Dim i As Integer
'Adding New Workbook
Set wkb = Workbooks.Add
With Me.ListBox1
For i = 1 To .ListCount - 1
If .Selected(i) Then
''///Sheet1 of the new wb will be the activesheet
ActiveSheet.Cells(r, 1).Value = .List(i, 0)
ActiveSheet.Cells(r, 2).Value = .List(i, 1)
ActiveSheet.Cells(r, 3).Value = .List(i, 2)
r = r + 1
End If
Next
End With
End Sub

Display More

Baski007 · Apr 30th 2020

Thanks for u reply mr.dangelor... it worked fine..

royUK · Apr 30th 2020

Have you tried my code?

dangelor · Apr 30th 2020

You're welcome. Glad to help!

Need a solution for copy the selected items in listbox to a newworkbook

Share

Tags