Hello,
I search for way to avoid problem of copying after TOTAL(lastrow) .
the code will copy the last row from listbox also I will fill combobox to copy before TOTALrow.
what I want when try to copy data from userform to sheet and there is no empty row before TOTAL row ,then should insert new row with the same formatting and borders and fill data into new inserted row and expand range for the formulas in TOTAL row.
and if there is empty row then just fill in empty row without insert new empty row to fill it.
Private Sub CommandButton1_Click()
Dim i As Long
With Me.ListBox1
For i = .ListCount - 1 To 0 Step -1
If .List(i, 0) <> "" Then
With Sheets(1)
.Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = ComboBox1.Value
.Cells(.Rows.Count, "C").End(xlUp).Offset(1).Value = ListBox1.List(i, 5)
.Cells(.Rows.Count, "D").End(xlUp).Offset(1).Value = ListBox1.List(i, 6)
.Cells(.Rows.Count, "E").End(xlUp).Offset(1).Value = ListBox1.List(i, 7)
End With
Exit For
End If
Next
End With
End Sub
thanks in advanced