hi people

can anyone help me how to simplify these codes?

Set oExcel = CreateObject("Excel.Application")
    Set oBook = ThisWorkbook
    Set osheet3 = oBook.Worksheets("DBASE")
    Set osheet4 = oBook.Worksheets("List Box Lookup Demonstration")
    Dim listIndex As Variant
      
    On Error Resume Next
    osheet3.Select
    Range("b65536").End(xlUp).Offset(1, 0).Select
    Selection.Value = ListBox2.List(0)
    Selection.Offset(0, -1).Value = TextBox2.Text
    Selection.Offset(0, 1).Value = TextBox3.Text
    Selection.Offset(1, 0).Value = ListBox2.List(1)
    Selection.Offset(2, 0).Value = ListBox2.List(2)
    Selection.Offset(3, 0).Value = ListBox2.List(3)
    Selection.Offset(4, 0).Value = ListBox2.List(4)
    Selection.Offset(5, 0).Value = ListBox2.List(5)
    Selection.Offset(6, 0).Value = ListBox2.List(6)
    Selection.Offset(7, 0).Value = ListBox2.List(7)
    Selection.Offset(8, 0).Value = ListBox2.List(8)
    Selection.Offset(9, 0).Value = ListBox2.List(9)
    Selection.Offset(10, 0).Value = ListBox2.List(10)
    Selection.Offset(11, 0).Value = ListBox2.List(11)
    Selection.Offset(12, 0).Value = ListBox2.List(12)
    Selection.Offset(13, 0).Value = ListBox2.List(13)
    Selection.Offset(14, 0).Value = ListBox2.List(14)
    Selection.Offset(15, 0).Value = ListBox2.List(15)
    Selection.Offset(16, 0).Value = ListBox2.List(16)
    Selection.Offset(17, 0).Value = ListBox2.List(17)
    Selection.Offset(18, 0).Value = ListBox2.List(18)
    Selection.Offset(19, 0).Value = ListBox2.List(19)
    Selection.Offset(20, 0).Value = ListBox2.List(20)
    
    Selection.Offset(0, -1).Select
    Selection.Copy
    Selection.Offset(0, 1).End(xlDown).Offset(-1, -1).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("C65536").End(xlUp).Select
    Selection.Copy
    Selection.Offset(0, -1).End(xlDown).Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

don't know if this is what you want

Set oExcel = CreateObject("Excel.Application")    
Set oBook = ThisWorkbook    
Set osheet3 = oBook.Worksheets("DBASE")   
Set osheet4 = oBook.Worksheets("List Box Lookup Demonstration")   
 Dim listIndex As Variant    
 Dim i as integer
      
    On Error Resume Next
    osheet3.Select
    Range("b:b").End(xlUp).Offset(1, 0).Select
    Selection.Value = ListBox2.List(0)
    Selection.Offset(0, -1).Value = TextBox2.Text

     for i = 1 to 20
    Selection.Offset(0, 1).Value = TextBox3.Text
    Selection.Offset(i, 0).Value = ListBox2.List(i)
     next i
    
    Selection.Offset(0, -1).Select
    Selection.Copy
    Selection.Offset(0, 1).End(xlDown).Offset(-1, -1).Select
    Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Range("C:C").End(xlUp).Select
    Selection.Copy
    Selection.Offset(0, -1).End(xlDown).Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

yah.. thanks much sir.

thats really it is.

thank you very much again..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.