locked
Copy Listview items to new row in excel sheet RRS feed

  • Question

  • Hi,

        The following code copies the item in listview to excel sheet, but each time it past the data on the same rows, I wish to copy the items in listview and copy those on excel sheet in a new row, thus the earlier data should remain there:

    Private Sub CommandButton1_Click()
    Dim i As Long
         Dim r As Long
         Dim c As Long
         
         Sheet14.Activate
         
         'Clear any existing data
         Range("A2:J100").CurrentRegion.ClearContents
         
         'List the column headers in the Second row, starting at A1
         With Me.ListView1
             For i = 1 To .ColumnHeaders.count
                 Cells(2, i).Value = .ColumnHeaders(i)
             Next i
         End With
         
         
         'List the selected items and subitems starting at Row 2
         r = 3
         With Me.ListView1
             For i = 1 To .ListItems.count
                 If .ListItems(i).Checked Then
                                 Cells(r, "A").Value = .ListItems(i).Text
                     For c = 1 To .ListItems(i).ListSubItems.count
                         Cells(r, c + 1).Value = .ListItems(i).ListSubItems(c).Text
                     Next c
                     r = r + 1
                 End If
             Next i
         End With
         
         
         Unload Me
         UserForm26.Show
    End Sub

    Thursday, November 27, 2014 5:51 AM

Answers

  • Hi Pankaj2610,

    To deal with the issue of overwrite the earlier row data, you could insert a new row, for example:

    LastRow.EntireRow.Insert

    More information about EntireRow, please refer to:

    # Range.EntireRow Property (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff836836(v=office.15).aspx   

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 28, 2014 7:04 AM

All replies

  • Hi,
        Hi,
            I tried with following code it works, it copies all the item in listview but it overwrites the earlier ones, Can you guide
    [CODE]

    Private Sub CommandButton2_Click()

    Dim LastRow As Object
    Dim i, j As Integer
    Set LastRow = Sheet10.Range("a65536").End(xlUp)
      For i = 1 To ListView1.ListItems.count
      Sheet10.Cells(i + 1, 1) = ListView1.ListItems(i).Text
      For j = 1 To 1
      Sheet10.Cells(i + 1, 2) = ListView1.ListItems(i).ListSubItems(1).Text
      Next j
     
      Next i

    End Sub

    [/CODE]
    Thursday, November 27, 2014 7:36 AM
  • Hi Pankaj2610,

    To deal with the issue of overwrite the earlier row data, you could insert a new row, for example:

    LastRow.EntireRow.Insert

    More information about EntireRow, please refer to:

    # Range.EntireRow Property (Excel)

    http://msdn.microsoft.com/en-us/library/office/ff836836(v=office.15).aspx   

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 28, 2014 7:04 AM