Listbox data copy to worksheet base on selected columns.

[asyamonique]

Good Day to All,
Below code populating a ListBox base on the ComboBox text on my userform.
Is it possible copy and paste the ListBox values(123) onto another worksheet named "info" from the cells A6:AF6 down when I click the button on userform.
ListBox populating with 35 columns, I need datas to copy for from 3th column to 34th column (C:AH) only!
And after copying those datas I need to create PDF file on my desktop named Staff List.
Many Thanks

Dim a, i As Long, ii As Long, b(), n As Long
ListBox1.Clear
With ComboBox1
If .Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Worksheets("list").Range("a:a"), .Text) = 0 Then
Exit Sub
End If
n = -1
a = Worksheets("list").Range("a1").Resize(Worksheets("list").Range("a" & Rows.Count).End(xlUp).Row, 35).Value
For i = 1 To UBound(a, 1)
If a(i, 1) = .Text Then
n = n + 2: ReDim Preserve b(1 To 35, 1 To n)
For ii = 1 To UBound(a, 2)
b(ii, n) = a(i, ii)
Next
End If
Next
End With
With ListBox1
.ColumnCount = 35
.ColumnWidths = "0;0;150;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;42;0"
.Column = b
End With

 

[DanteAmor]

Try this:

Private Sub CommandButton1_Click()
  Dim i As Long, j As Long
  Dim b As Variant
  
  If ListBox1.ListCount = 0 Then Exit Sub
  
  ReDim b(1 To ListBox1.ListCount, 1 To 32)
  For i = 0 To ListBox1.ListCount - 1
    For j = 0 To 31
      b(i + 1, j + 1) = ListBox1.List(i, j + 2)
    Next
  Next
  Sheets("info").Range("C6").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

 

[asyamonique]

Thanks dante,
It works well.
About creating pdf?

 

[DanteAmor]

About creating pdf?

I forgot that part.

Here is the full code:

Private Sub CommandButton1_Click()
  Dim i As Long, j As Long
  Dim b As Variant
  Dim sPath As String, sName As String
  
  If ListBox1.ListCount = 0 Then Exit Sub
  
  ReDim b(1 To ListBox1.ListCount, 1 To 32)
  For i = 0 To ListBox1.ListCount - 1
    For j = 0 To 31
      b(i + 1, j + 1) = ListBox1.List(i, j + 2)
    Next
  Next
  
  sPath = Environ("USERPROFILE") & "\Desktop\"
  sName = sPath & "Staff List.pdf"
  With Sheets("info")
    .Range("C6").Resize(UBound(b, 1), UBound(b, 2)).Value = b
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=sName, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
  MsgBox "Pdf created"
End Sub