copy data from listbox to sheet after headers and before Total row

[Mussa]

Hi
I have this code to copy data from listbox to sheet

Private Sub cmdSend_Click()
    With Me.ListBox1
        If .ListCount > 0 Then
            Sheets("SL").[a7].Resize(.ListCount, .ColumnCount) = .List
        End If
    End With
End Sub

bu I need adapting by copy after row6 and before total row and if I have rows in listbox more than empty rows inside the sheet then should insert new rows with the same borders ,if I have rows in listbox less than empty rows inside the sheet then should delete empty rows

here is the structure inside sheet

Sample.xlsm
	A	B	C	D	E
6	ITEM	ID	QTY	UNIT PRICE	TOTAL
7
8
9
10	TOTAL				.00
SL

Cell Formulas
Range	Formula
E10	E10	=SUM(E7:E9)

when fill listbox

the result should be

Sample.xlsm
	A	B	C	D	E
6	ITEM	ID	QTY	UNIT PRICE	TOTAL
7	1	ABSS-100	10	10.00	100.00
8	2	*** TYYY 2000	12	22.00	264.00
9	3	ASDE9900/1200	2	22.00	44.00
10	4	XDD*7777	2	12.00	24.00
11	TOTAL				432.00
SL

Cell Formulas
Range	Formula
E11	E11	=SUM(E7:E10)

 

[jolivanes]

Re: "it delete the borders"
Show the code with which you tried to fix that so we can fix it for you.

 

[Mussa]

Show the code with which you tried to fix that so we can fix it for you.

I don't understand it !
just I used code in post #27
I will make borders manually bfore I run the code .

 

[jolivanes]

Add the four lines as indicated so the code should look like this and try.

Private Sub CommandButton1_Click()
Dim c As Range    '<---- Add this line
Dim sh4 As Worksheet    '<---- Change as required
Set sh4 = Worksheets("Sheet4")    '<---- Change as required
With sh4    '<---- Change as required
    .Cells(1).Offset(6).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 6, 5).Delete Shift:=xlUp
    .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(Me.ListBox1.ListCount, Me.ListBox1.ColumnCount).Value = ListBox1.List
With .Cells(Rows.Count, 1).End(xlUp).Offset(1)
    .Value = "TOTAL"
    .Interior.Color = RGB(255, 255, 0)
    .Offset(, 4).Formula = "=SUM(E7:E" & Cells(Rows.Count, 1).End(xlUp).Row - 1 & ")"
End With
End With
Cells(1).Offset(6).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 6, 5).HorizontalAlignment = xlCenter
    For Each c In Range("A7:E" & Cells(Rows.Count, 1).End(xlUp).Row)    '<---- Add this line
        If Len(c) <> 0 Then c.BorderAround ColorIndex:=1, Weight:=xlThin    '<---- Add this line
    Next c    '<---- Add this line
Unload Me
End Sub

 

[Mussa]

Add the four lines as indicated so the code should look like this and try.

sorry about delaying !
the code works greatly when add before TOTAL row , but when delete before TOTAL row will show application defined error in this line

.Cells(1).Offset(6).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 6, 5).Delete Shift:=xlUp