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

Participate now!

Share

Tags

Users Viewing This Thread