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]

With Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    .Interior.Color = RGB(255,255,0)    '<----- Add this line    
    .Value = "TOTAL"
    .Offset(, 4).Formula = "=SUM(E7:E" & Cells(Rows.Count, 1).End(xlUp).Row - 1 & ")"
End With

 

[Mussa]

thanks , but still keep color when move TOTAL row down as in post # 8 , also if move up TOTAL row will leave color under TOTAL row !

 

[jolivanes]

Change this section, or whatever it has been changed to

With Sheets("Sheet1")
    .Columns(1).Find("TOTAL", , , 1).Resize(, 5).ClearContents
    .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(Me.ListBox1.ListCount, Me.ListBox1.ColumnCount).Value = ListBox1.List
End With

to so

With Sheets("Sheet1").Columns(1).Find("TOTAL", , , 1).Resize(, 5)
    .ClearContents
    .Interior.Color = xlNone
End With

 

[Mussa]

will delete TOTAL word and pops error object variable or with block variable not set

With Sheets("Sheet1").Columns(1).Find("TOTAL", , , 1).Resize(, 5)

 

[jolivanes]

Did you change the sheet and column references?

 

[Mussa]

Just sheet name .

 

[jolivanes]

Show us the whole code.

 

[Mussa]

here is the code

With Sheets("SL")
    .Columns(1).Find("TOTAL", , , 1).Resize(, 5).ClearContents
    .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(Me.ListBox1.ListCount, Me.ListBox1.ColumnCount).Value = ListBox1.List
End With
With Sheets("SL").Columns(1).Find("TOTAL", , , 1).Resize(, 5)
    .ClearContents
    .Interior.Color = xlNone
End With

 

[jolivanes]

As suggested in Post #14, change the top 4 lines to the bottom 4 lines.
Delete the top 4 lines of the code you have in Post #18 so what you'll have left is

With Sheets("SL").Columns(1).Find("TOTAL", , , 1).Resize(, 5)
    .ClearContents
    .Interior.Color = xlNone
End With

 

[Mussa]

Ok , but will show error as in post # 14