Problem with adding line in macro

[asmales]

I am re-posting this as a good member informed me that an attachment had not been received

I am not an expert at excel macros and I am at a loss as to how to go forward.
I have attached the part of the spreadsheet and the macro written so far. When I have entered data into rows I3 to M3 I have no problem.
When I Pressthe macro to enter the data into the information set below it enters it into Row 20 I3 to M3, which a want. If I repeat this again a number of times it will keep adding the rows and eventually enter over the TOTAL row in row 22.
I want to be able to move the row with the TOTAL (row 20) down after each transaction to prevent this from happening.
As I enter a number of rows I want the total (which shows in row I22 to auto sum the number of rows that have been entered.

I have tried numerous ways but it is beyond me.


Sub SalesTransaction()

Worksheets("GENERAL SALES").Unprotect "AMBIT1ON"

Set wsSALES = Worksheets("GENERAL SALES")

With wsSALES

Set rng = .Range("I3:I29").Find(.Range("I3").Value, , , xlWhole).Offset(8, 0)

tmprow = rng.End(xlDown).Row

If tmprow = Rows.Count Then

tmprow = .Range("I" & Rows.Count).End(xlUp).Row + 1

ElseIf .Range("I" & tmprow).Value = "CODE" Then

tmprow = .Range("I" & tmprow).End(xlUp).Row + 1

ElseIf rng.Value <> "" Then

tmprow = rng.End(xlDown).Row + 1

End If

.Range("I" & tmprow & ":J" & tmprow & ":K" & tmprow & ":L" & tmprow & ":M" & tmprow) = Range("I3:M3").Value

End With

Range("I3").ClearContents
Range("J3").ClearContents

Range("L3").ClearContents

End Sub

Since this the first time that I have used this post thread forgive me if I make a mess of this

SALES1.xlsm
	B
15
GENERAL SALES

 

[RudRud]

Hi, It would be good if you can provide more sample data through mini sheet via XLBB with expected result for better understanding

Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest January 2021 v 2.0 )

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.