PDA

View Full Version : [Help]Export ListBox contents to new workbook via CommandButton!



newskin
07-22-2015, 01:21 AM
On page "Tim_TKSua" , i had try code(attachment file) want to Export ListBox contents to new workbook via CommandButton1, but when i hitting commandbutton1 it will be error(image attachment).Pls help me. thanks all.
13969

p45cal
07-22-2015, 03:39 AM
Same rules here as on Ozgrid; say where you've cross posted:
http://www.ozgrid.com/forum/showthread.php?t=195919
http://forum.chandoo.org/threads/help-export-listbox-contents-to-new-workbook-via-commandbutton.24611/

p45cal
07-22-2015, 04:52 AM
Your ListBox1.Rowsource property is an empty string because you assigned an array to ListBox1.List.
Try this alternative for the same procedure where the error occurs:
Private Sub CommandButton1_Click()
Dim aSheet, aWind
Dim oW

Set aWind = ThisWorkbook
Set aSheet = ActiveSheet
Set oW = Workbooks.Add
cvcv = ListBox1.List
Sheet1.Range("A1:R1").Copy ' headers
With oW.Sheets(1).Cells(1).Resize(, 18)
.PasteSpecial xlAll
.PasteSpecial Paste:=xlPasteColumnWidths
.Offset(1).Resize(UBound(cvcv) + 1, UBound(cvcv, 2) + 1) = cvcv
Sheet1.Range("A2:R2").Copy ' for formatting only.
.Offset(1).Resize(UBound(cvcv) + 1).PasteSpecial Paste:=xlPasteFormats
End With
oW.Sheets(1).Name = Format(Now(), "yyyy-mm-dd") & " Transfer"
Cells(1, 1).Select
aWind.Activate
Application.CutCopyMode = False
aSheet.Activate
oW.Activate
End Sub