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]

Put "TOTAL" and the formula in the sheet after the data has been transferred into it. Make that part of your code.

 

[Mussa]

Make that part of your code.

that waht I look for to do that by experts.

 

[jolivanes]

Check and change references as required. Try on a copy of your original workbook.

In a regular Module.

Sub Show_It()
    UserForm1.Show
End Sub

A UserForm1 With a ListBox1 and a CommandButton1
In the Properties Window for the Listbox, set ColumnCount and ColumnWidths to desired amount(s)

In the UserForm Module (Change the Range to the needed amount and where the data is.)

Private Sub UserForm_Initialize()
Me.ListBox1.List = Sheets("Sheet1").Range("L2:N" & Sheets("Sheet1").Cells(Rows.Count, 12).End(xlUp).Row).Value
End Sub

Private Sub CommandButton1_Click()
If Cells(Rows.Count, 1).End(xlUp).Value <> "TOTAL" Then Cells(Rows.Count, 1).End(xlUp).Offset(2).Value = "TOTAL"
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
With Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    .Value = "TOTAL"
    .Offset(, 4).Formula = "=SUM(E7:E" & Cells(Rows.Count, 1).End(xlUp).Row - 1 & ")"
End With
Unload Me
End Sub

 

[Mussa]

thanks but it will add TOTAL word even if it's existed .
based on your code if it isn't existed will add otherwise should ignore it.
last thing how avoid copy the headers when show in listbox .

 

[jolivanes]

Re: "thanks but it will add TOTAL word even if it's existed ." It should not if your actual is like you showed in Post #1. (Last value in Column A)

Re: "last thing how avoid copy the headers when show in listbox ." Change the range to the actual Range. In Post #4 it has L2:N ....

 

[jolivanes]

If Cells(Rows.Count, 1).End(xlUp).Value <> "TOTAL" Then Cells(Rows.Count, 1).End(xlUp).Offset(2).Value = "TOTAL"

You can leave this line out if there is a guarantee that it has "TOTAL" as a last Value in Column A.

 

[Mussa]

You can leave this line out if there is a guarantee that it has "TOTAL" as a last Value in Column A.

ok.

before

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

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

after

Sample1.xlsm
	A	B	C	D	E
6	ITEM	ID	QTY	UNIT PRICE	TOTAL
7	1	ABSS-100	22	20.00	440.00
8	2	*** TYYY 2000	33	20.00	660.00
9	3	ASDE9900/1200	10	20	200.00
10	TOTAL				1,300.00
SL

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

as you see will move TOTAL word down , but should add rows before TOTAL row !

 

[jolivanes]

"TOTAL" is down. Do you mean the yellow color?

 

[Mussa]

yes , you can see that from the picture.