VBA Listbox copy selected row and paste to sheet

[pure vito]

Hi All,

I've seen this question many times, I've been trying hard to find the code I need but it seems everyone's request is unique in this case I haven't been able to doctor any code that I have found,

So I have come to you for help please,

I need to copy selected data from Listbox1, the listbox is populated with data I would like to copy the selected data and paste it into sheet "Rolecheck",

It would paste into columns A1-D1 and when a new row is selected in the listbox (running the code again) it would overwrite the data in A1-D1 only ever leaving one row from the listbox in the sheet,

Hope this makes sense and as always your time is very much appreciated thanks in advance.

 

[dmt32]

Hi,
see if following will do what you want

Place in your userforms code page

Private Sub ListBox1_Click()
    Dim wsRolecheck As Worksheet
    
    Set wsRolecheck = Worksheets("Rolecheck")

    With Me.ListBox1
     Worksheets("SHEETNAME").Cells(.ListIndex + 2, 1).Resize(, .ColumnCount + 1).Copy wsRolecheck.Range("A1")
    End With
End Sub

Change SHEETNAME as required
(note if you have already declared an object variable for the source sheet in your code then no need to repeat hard coding worksheet as you can use that instead.)

Code should when row selected, copy the row data from the source sheet to your RoleCheck sheet A1:D1

Dave

 

[pure vito]

Hi thanks for taking the time it seems to work but whenever I click on the listbox to select the row it comes up with this error,

"JobRequirements" is the sheet populating the listbox,

Private Sub ListBox1_Click()
    Dim wsRolecheck As Worksheet
    
    Set wsRolecheck = Worksheets("Rolecheck")

    With Me.ListBox1
    [COLOR=rgb(209, 72, 65)] Worksheets("JobRequirements").Cells(.ListIndex + 2, 1).Resize(, .ColumnCount + 1).Copy wsRolecheck.Range("A1")[/COLOR]
    End With
End Sub

This is all the code I have in the user form I think what your saying is the references in the first code will effect the new one?

I'm just not sure how to combine these if that is the case, again thank you for your help

Private Sub showall_Click()
' ---------- Show All ---------------
Dim sh As Worksheet
Set sh = Sheets("JobRequirements")
Dim iRow As Long
iRow = sh.Range("A" & Rows.Count).End(xlUp).Row
'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 5
       .ColumnHeads = True
       .ColumnWidths = "120,120,120,120,120"
       .RowSource = "JobRequirements!A2:E2" & iRow
   End With
   'Now run & See
End Sub

Private Sub ListBox1_Click()
    Dim wsRolecheck As Worksheet
    
    Set wsRolecheck = Worksheets("Rolecheck")

    With Me.ListBox1
     Worksheets("JobRequirements").Cells(.ListIndex + 2, 1).Resize(, .ColumnCount + 1).Copy wsRolecheck.Range("A1")
    End With
End Sub

 

[dmt32]

That's Interesting - try replacing your codes with following & see if resolves

Dim sh As Worksheet
Private Sub showall_Click()
' ---------- Show All ---------------

Dim iRow As Long
iRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 5
       .ColumnHeads = True
       .ColumnWidths = "120,120,120,120,120"
       .RowSource = "JobRequirements!A2:E" & iRow
   End With
   'Now run & See
End Sub

Private Sub ListBox1_Click()
    Dim wsRolecheck As Worksheet
    
    Set wsRolecheck = ThisWorkbook.Worksheets("Rolecheck")

     wsRolecheck.Range("A1:D1").Value = _
     sh.Cells(Me.ListBox1.ListIndex + 2, 1).Resize(, 3).Value

End Sub

Private Sub UserForm_Initialize()
    Set sh = ThisWorkbook.Worksheets("JobRequirements")
End Sub

Dave

 

[pure vito]

Seem to be getting this error now also the list box wasn't populating I had to add the missing lines at the top of the code,

I'm not really sure what's going on with this? again I'm getting this error when I click the Listbox

Private Sub showall_Click()
' ---------- Show All ---------------

Dim sh As Worksheet
Set sh = Sheets("JobRequirements")
Dim iRow As Long
iRow = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
'to populate listbox  from Database sheet
   With Me.ListBox1
       .ColumnCount = 5
       .ColumnHeads = True
       .ColumnWidths = "120,120,120,120,120"
       .RowSource = "JobRequirements!A2:E" & iRow
   End With
   'Now run & See
End Sub

Private Sub ListBox1_Click()
    Dim wsRolecheck As Worksheet
    
    Set wsRolecheck = ThisWorkbook.Worksheets("Rolecheck")

     wsRolecheck.Range("A1:D1").Value = _
     sh.Cells(Me.ListBox1.ListIndex + 2, 1).Resize(, 3).Value

End Sub

Private Sub UserForm_Initialize()
    Set sh = ThisWorkbook.Worksheets("JobRequirements")
End Sub

 

[dmt32]

If you look at the codes I posted in #post4 you will see this variable declaration sitting at the very TOP

Dim sh As Worksheet

which you have not copied.

This line MUST be placed at the TOP of your Userforms code page OUTSIDE any procedure

Dave

 

[pure vito]

Sorry I did copy the whole code i just didn't select it all in my reply it was always there, it was another code I had to delete relating to something else in the form but it's working now thank you
very much just one issue two of the columns are populating as #NA any idea what that might be?

 

[pure vito]

I'll work on that one thank you I have added a 5th column and shifted the data to B1-F1, if i can't work this one out there's no help for me

 

[dmt32]

I'll work on that one thank you I have added a 5th column and shifted the data to B1-F1, if i can't work this one out there's no help for me

wsRolecheck.Range("B1:F1").Value = _
     sh.Cells(Me.ListBox1.ListIndex + 2, 1).Resize(, 5).Value

should hopefully work for you

Dave

 

[pure vito]

Really appreciate the help thank you Dave