Export contents of listbox to new excel workbook

[jstn]

I've got a userform that searches a database for certain criteria and displays the results in a list box with 7 columns. I'm now trying to figure out the code to export the contents of that listbox into a new workbook executing with a command button, appreciate any help!

[AlphaFrog]

Try something like this...

Private Sub CommandButton1_Click()
    With Me.ListBox1
        Workbooks.Add xlWBATWorksheet
        Sheets(1).Range("A1").Resize(.ListCount, .ColumnCount).Value = .List
    End With
End Sub

[mehmetcik]

Hi

This is all you need to fill and store entries from a listbox.
I have included a few redundant lines to give you some hints.

Private Sub CommandButton1_Click()
MyName = ActiveWorkbook.Name
Workbooks.Add
NewName = ActiveWorkbook.Name
Entries = ListBox1.ListCount
For Count = 0 To Entries - 1
For count2 = 0 To 6

Cells(Count + 1, count2 + 1).Value = ListBox1.List(Count, count2)
Next
Next

End Sub

Private Sub UserForm_Activate()
ListBox1.List = Range("A1:G18").Value
End Sub

This code stores the whole listbox. How often will you need to do that?

Private Sub CommandButton1_Click()

Workbooks.Add
Range("A1:A" & ListBox1.ListCount).Value = ListBox1.List
End Sub

[jstn]

That does the trick, thank you! seems so simple, I suspected it would be but couldn't nail it down.
The whole list box contents is what I needed, the filtering is done on the userform.

A small adjustment if possible, the list box doesn't have headers, but the database it pulls from does, could I have the headers added into the exported file? Could we also size the columns in the workbook to fit the data?

[AlphaFrog]

A small adjustment if possible, the list box doesn't have headers, but the database it pulls from does, could I have the headers added into the exported file? Could we also size the columns in the workbook to fit the data?

Private Sub CommandButton1_Click()
    With ActiveSheet
        Workbooks.Add xlWBATWorksheet
        .Rows(1).Copy Destination:=ActiveSheet.Rows(1)
    End With
    With Me.ListBox1
        ActiveSheet.Range("A2").Resize(.ListCount, .ColumnCount).Value = .List
    End With
    ActiveSheet.Columns.AutoFit
End Sub

[jstn]

Thank you again, that's perfect! Is there a way to declare the column headers?

[AlphaFrog]

You're welcome.

Private Sub CommandButton1_Click()
    Workbooks.Add xlWBATWorksheet
    Range("A1:G1").Value = Array("Header1", "Header2", "Header3", "Header4", "Header5", "Header6", "Header7")
    With Me.ListBox1
        ActiveSheet.Range("A2").Resize(.ListCount, .ColumnCount).Value = .List
    End With
    ActiveSheet.Columns.AutoFit
End Sub