Listbox export

[GhostofDoom]

Hello,

i like some help for export the whole listbox into my sheet
if possible we like to import in the correct cells/column of the sheet

hope we make a good point :)

our code



Dim lngItem As Long


For lngItem = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(lngItem) Then
With Worksheets("2020")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = ListBox2.List(lngItem, 1)
End With
End If
Next lngItem


this add the selected to the sheet
but we can't manage to add the one that are displayed in the listbox
to the correct columns of the sheet
and it starts on row 4 instead of row 5

our sheet has 5 columns
also column B we like to add autonumeric count numbers when items are added
listbox2.column(number) we like to add into the column we like into the sheet


thank you

[GhostofDoom]

I have created an sample book
from the sheet TestingImport
the columns J K

has to be imported into the sheet 2020
on column D E

from TestingImport Column F to sheet 2020 column A
and from TestingImport column D to sheet 2020 column C

and sheet 2020 column B has to be auto-numeric number when added the data
hope it helps :)

Thank you

25785

[GhostofDoom]

hello,

i got it working



Dim lItem As Long


If Worksheets("2020").Range("B5").Value = "" Then
Worksheets("2020").Range("A5:G5").FormulaArray = "1"
End If


For lItem = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(lItem) Then
With Worksheets("2020")
.Cells(lItem + 5, 1) = ListBox2.List(lItem, 5)
Drng.End(xlDown).Offset(5, 2).Value = Drng.End(xlDown).Offset(-1, 2).Value + 1 'autonumber must come on row 5 at cell 2
.Cells(lItem + 5, 3) = ListBox2.List(lItem, 3)
.Cells(lItem + 5, 4) = ListBox2.List(lItem, 9)
.Cells(lItem + 5, 5) = ListBox2.List(lItem, 10)

End With
End If
Next lItem


but i can't get the autonumber ?
anyone please

thank you

[GhostofDoom]

Hello again,

well seems we got it working



.Cells(lItem + 5, 2) = lItem + 1




seems so simple if you think a little harder :)

but i hope i get some help with the next question
how can i open or create a file and add this code into the new file

code we use for import is


Dim xlSheet As Worksheet


On Error GoTo err_Handler
Set xlSheet = ActiveWorkbook.Worksheets("CopySheet") 'the current workbook we have this sheet in it
xlSheet.Visible = xlSheetVisible
xlSheet.Copy After:=ActiveWorkbook.Sheets("Year") ' in the file we have created
ActiveSheet.Name = NameSheet '& NameAfter 'want to add into the new file
CreateSheetp = True




lbl_Exit:
Set xlSheet = Nothing
Exit Function
err_Handler:
Beep
Err.Clear
Application.DisplayAlerts = False
ActiveWorkbook.Worksheets("CopySheet (2)").Delete 'delete in the new file if exists
Application.DisplayAlerts = True
CreateSheetp = False
GoTo lbl_Exit

[snb]

A oneliner suffices:


Sub M_snb()
sheet1.cells(1).resize(listbox1.listcount,ubound(listbox1.list,2)+1)=listbo x1.list
End Sub

[GhostofDoom]

Hello,

got it working so far to adding the copy sheet into my new workbook
but i always get a message its says sheet overwrite ? and i get a dialog to say 'yes' or add new name
but we got this from our activeworkbook (ThisWorkbook) ??
so didn't make any sense



Set NewWbook = Workbooks.Open(filepaths)
NewWbook .Activate
ThisWorkbook.Sheets("CopySheet").Copy Before:=NewWbook .Sheets("Retours")
NewWbook .ActiveSheet.Name = "2020"

[GhostofDoom]

Okay can be added to *solved*

we have managed the error by adding


Application.DisplayAlerts = False


so creating and copy sheets working now

thanks all

[Paul_Hossler]

You can mark it SOLVED by using [Thread Tools] above your first post

[GhostofDoom]

You can mark it SOLVED by using [Thread Tools] above your first post

Ooooh Thanks Paul_Hossler i didn't known that , thanks :thumb