Insert ow where I need after previous insert row

October 20, 2017 at 10:15:01
Specs: Windows 10
Please Help,

I have been stuck for some time now. I have an excel spreadsheet with 30+ sheets.
i use it for bookkeeping, and I have it set up so that if salesperson a sells x amount it
transposes to a monthly sheet. I have it set up so that it will insert lines for each
additional sales person's x amount. The problem I've run into is that if I use this for
sheet 3 the inserted row is out of order. I cannot find a way to insert the line where
I need it. For instance, if sheet 2 inserts two rows to "Monthly" when it changes
the row number that sheet 3 would insert to. I've included what I have so far, if
anyone can help I would greatly appreciate it.

Sub Test()

If Sheets("1").Range("U2") = "Julia" Then
Else
    If Sheets("1").Range("D28").Value > 0 Then
    Else
        If Sheets("Month").Range("I3") > 0 Then
            Sheets("Month").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            Sheets("Month").Range("I3").Value = Sheets("1").Range("D28").Value
            Sheets("Month").Range("X3").Value = Sheets("1").Range("D27").Value
            End If
        End If
    End If
If Sheets("1").Range("U2") = "Jeremy" Then
Else
    If Sheets("1").Range("E28").Value > 0 Then
        Sheets("Month").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Month").Range("I3").Value = Sheets("1").Range("E28").Value
        Sheets("Month").Range("X3").Value = Sheets("1").Range("E27").Value
        End If
    End If
If Sheets("1").Range("U2") = "Ed" Then
Else
    If Sheets("1").Range("F28").Value > 0 Then
        Sheets("Month").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Month").Range("I3").Value = Sheets("1").Range("F28").Value
        Sheets("Month").Range("X3").Value = Sheets("1").Range("F27").Value
        End If
    End If
If Sheets("1").Range("U2") = "Lois" Then
Else
    If Sheets("1").Range("G28").Value > 0 Then
        Sheets("Month").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Month").Range("I3").Value = Sheets("1").Range("G28").Value
        Sheets("Month").Range("X3").Value = Sheets("1").Range("G27").Value
        End If
    End If
If Sheets("1").Range("U2") = "Dennis" Then
Else
    If Sheets("1").Range("H28").Value > 0 Then
        Sheets("Month").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Month").Range("I3").Value = Sheets("1").Range("H28").Value
        Sheets("Month").Range("X3").Value = Sheets("1").Range("H27").Value
        End If
    End If
If Sheets("1").Range("U2") = "Kanani" Then
Else
    If Sheets("1").Range("I28").Value > 0 Then
        Sheets("Month").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Month").Range("I3").Value = Sheets("1").Range("I28").Value
        Sheets("Month").Range("X3").Value = Sheets("1").Range("I27").Value
        End If
    End If
If Sheets("1").Range("U2") = "Terri" Then
Else
    If Sheets("1").Range("J28").Value > 0 Then
        Sheets("Month").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Month").Range("I3").Value = Sheets("1").Range("J28").Value
        Sheets("Month").Range("X3").Value = Sheets("1").Range("J27").Value
        End If
    End If
If Sheets("1").Range("U2") = "Phil" Then
Else
    If Sheets("1").Range("K28").Value > 0 Then
        Sheets("Month").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Month").Range("I3").Value = Sheets("1").Range("K28").Value
        Sheets("Month").Range("X3").Value = Sheets("1").Range("K27").Value
        End If
    End If


End Sub

Thank You


See More: Insert ow where I need after previous insert row

Reply ↓  Report •

#1
October 27, 2017 at 06:31:32
Could you explain this a little better, maybe even show us how your worksheets look?

If I understand it correct, you want to insert into Sheet Month, the sales each sale person has made, but it should be inserted under their chunk of data so that, all rows are sorted by sales person?

       A          B           C             D
1     John       200          x              y
2     John       80           a              b
3     Sally      200          1              2
4     Sally      100          w             c
5

Now if Johns makes another sale, you want to insert that into row 3 and move the two records for Sally down?

Am I correct?


Reply ↓  Report •

#2
October 31, 2017 at 07:51:21
Mon 1
Tue 2
Wed 3
Thur 4

If Julia sells 2500 on Monday and Jeremy sells 5000 insert line between Mon-Tue. If on Tues same thing insert between Tue-Wed. everything i try just keeps inserting based on line number instead of date


Reply ↓  Report •

#3
October 31, 2017 at 07:57:39
Just above the textbox you type in is a tag call "pre" please use this tag to show us your data, just like I have in #1, I have presented data is a structured way.

What I would like to see is the layout of your workbook, please include row number and column letters.

Then present some sales data and show us using the pre tag what you would expect to see as a final result. Keep in mind from where we are sitting we cannot see your workbook so it is vital you provide as much information as possible.

How is the code meant to determine when the sale was made, ie if the sale was on Monday, Tuesday etc all this information is relevant and needed before we can even begin to assist you.


Reply ↓  Report •

Related Solutions

#4
October 31, 2017 at 14:47:08
Sheet (Monthly)													
Date		Sales	Tax	Subtotal	Delivery	Total   	Cost	Profit	Margin	Cust	Sold	State 4%	RSA
Sun	1	$330.00	$13.20	$343.20 	$60.00  	$403.20 	$240.00	$0.00	27%	5	3	$330.00	Julia
Mon	2	$630.00	$25.20	$655.20 	$60.00  	$715.20 	$240.00	$130.00	62%	2	2	$630.00	Ed
Tue	3	$930.00	$37.20	$967.20 	$60.00  	$1,027.20	$240.00	$225.00	74%	3	3	$930.00	Jeremy


Sheet (1)													
Sales	Rate	Tax	Subtotal	Delivery	Total	Cost	Profit	Margin	State 4%			RSA	
										Today's Salesperson		Julia	
$100.00	4.0%	$4.00	$104.00	$15.00	$119.00	$75.00	$25.00	25%	$100.00			Jeremy	
$110.00	4.0%	$4.40	$114.40	$20.00	$134.40	$80.00	$30.00	27%	$110.00			Ed	
$120.00	4.0%	$4.80	$124.80	$25.00	$149.80	$85.00	$35.00	29%	$120.00			Lois	


Sheet (2)													
Sales	Rate	Tax	Subtotal	Delivery	Total	Cost	Profit	Margin	State 4%			RSA	
										Today's Salesperson		Ed	
$200.00	4.0%	$8.00	$208.00	$15.00	$223.00	$75.00	$125.00	63%	$200.00			Jeremy	
$210.00	4.0%	$8.40	$218.40	$20.00	$238.40	$80.00	$130.00	62%	$210.00			Ed	
$220.00	4.0%	$8.80	$228.80	$25.00	$253.80	$85.00	$135.00	61%	$220.00			Lois	


Sheet (3)													
Sales	Rate	Tax	Subtotal	Delivery	Total	Cost	Profit	Margin	State 4%			RSA	
										Today's Salesperson		Jeremy	
$300.00	4.0%	$12.00	$312.00	$15.00	$327.00	$75.00	$225.00	75%	$300.00			Jeremy	
$310.00	4.0%	$12.40	$322.40	$20.00	$342.40	$80.00	$230.00	74%	$310.00			Ed	
$320.00	4.0%	$12.80	$332.80	$25.00	$357.80	$85.00	$235.00	73%	$320.00			Lois	

I'm Trying so that if on Sun 1 there are three salespeople the sheet will automatically insert lines for each additional salesperson between Sun 1 and Mon 2. I have that part figured out. Where I'm stuck is if on Mon 2 three more sales are made it inserts on Sheet (Monthly) between Mon 2 and Tue 3. if i use insert line at ("4:4"), and lines were inserted between Sun-Mon, it throws Mon-Tue off. Thanks for working with me on this.


Reply ↓  Report •

#5
November 1, 2017 at 03:47:44
Ok a few questions

Sheet (3)													
Sales	Rate	Tax	Subtotal	Delivery	Total	Cost	Profit	Margin	State 4%			RSA	
										Today's Salesperson		Jeremy	
$300.00	4.0%	$12.00	$312.00	$15.00	$327.00	$75.00	$225.00	75%	$300.00			Jeremy	
$310.00	4.0%	$12.40	$322.40	$20.00	$342.40	$80.00	$230.00	74%	$310.00			Ed	
$320.00	4.0%	$12.80	$332.80	$25.00	$357.80	$85.00	$235.00	73%	$320.00			Lois	

1) How do you determine which day the sale was made? there isn't a column I can see on any sheet that identifies the day of sale eg Monday, Tuesday etc

2) I am a little confused here

if on Sun 1 there are three salespeople the sheet will automatically insert lines for each additional salesperson between Sun 1 and Mon 2 

Ok I get this bit, but then

Where I'm stuck is if on Mon 2 three more sales are made it inserts on Sheet (Monthly) between Mon 2 and Tue 3.

This seems correct to me, unless I am missing something?

If I make sale on Sunday, insert between Sunday and Monday
If I make a sale on Monday, insert between Monday and Tuesday

I don't see the problem with this?

You need to provide your logic as I don't understand why the above is incorrect.

It maybe worth sending me your workbooks, unfortunately where I work the network blocks all file sharing sites so the best I can do is provide you with an email address for you to send to, you can upload your files to ZippyShare (Google it) for others to see. ensure you remove and sensitive and personal data from them. I will PM you my email address (I do not monitor that email account so if you send something notify me here)


Reply ↓  Report •

Ask Question