Copy function before inserting row corrupting macro

[nosense]

Hi there,

I want to make a code that copies a range when conditions are met and then iserts the row below these conditions, selects cell in new row and paste copied selection.

When I am doing insert row when conditions are met then it seems to work i.e

For i = 500 To 9 Step -1
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then Rows(i + 1).Insert
   
Next

But the problem starts when I add the other conditions so I have :

 For i = 9 To 500
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then Range(Cells(i, 3), Cells(i, 11)).Copy

Next

For i = 500 To 9 Step -1
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then Rows(i + 1).Insert
Next

Seems like it is adding a row but pasting automatically copied cells from first condition into cell ("A",i).






Next

[pjwhitfield]

I may be wrong but you appear to be doing this in two seperate loops which will cause you problems.

Imagine the 1st loop finds 5 matching items, the 1st at row 10 so it adds in a new row at 11 (so the old row 11 becomes 12), it then does the same at rows 20,30,40 and 50

Your 2nd loop should really now loop from 505 to 9 as there are now an additional 5 rows in there that werent before the 1st loop ran.

Im not sure if this is the reason for your problem but its a problem in itself.

You should be carrying out both actions in the same IF statement so theyre done as one. You probably also dont need to insert a row as a seperate action.

Your best bet is to do the copy and paste actions on a test row while recording a Macro then amend the code in there to fit into your IF statement.

[nosense]

I may be wrong but you appear to be doing this in two seperate loops which will cause you problems.

Imagine the 1st loop finds 5 matching items, the 1st at row 10 so it adds in a new row at 11 (so the old row 11 becomes 12), it then does the same at rows 20,30,40 and 50

Your 2nd loop should really now loop from 505 to 9 as there are now an additional 5 rows in there that werent before the 1st loop ran.



Im not sure if this is the reason for your problem but its a problem in itself.

You should be carrying out both actions in the same IF statement so theyre done as one. You probably also dont need to insert a row as a seperate action.

Your best bet is to do the copy and paste actions on a test row while recording a Macro then amend the code in there to fit into your IF statement.

Thanks for the reply. Your sugesstion is invalid as nature is that there is always only one entry that meets conditions.

If you look at the code, it is first finding the row that meets conditions and copies a range from this row.

For i = 9 To 500
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then Range(Cells(i, 3), Cells(i, 11)).Copy

Next

Second "IF" is inserting entire row below the row that meets same conditions

For i = 500 To 9 Step -1
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then Rows(i + 1).Insert
Next

Problem is that "insert row" macro is working fine if the first statement is not there which does not make sense because this firsts statement only copies the range

[pjwhitfield]

Thanks for the reply. Your sugesstion is invalid as nature is that there is always only one entry that meets conditions.

If you look at the code, it is first finding the row that meets conditions and copies a range from this row.

For i = 9 To 500
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then Range(Cells(i, 3), Cells(i, 11)).Copy

Next

Second "IF" is inserting entire row below the row that meets same conditions

For i = 500 To 9 Step -1
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then Rows(i + 1).Insert
Next

Problem is that "insert row" macro is working fine if the first statement is not there which does not make sense because this firsts statement only copies the range

Its highly valid.

Your code currently runs through 492 rows, from row 9 to row 500 and everytime it finds a matching entry it copies some data...however it can only hold one set of copied data so we'll say it finds a match at row 50, it copies the range "C50: K50" and then moves to the next row and so on....it then finds another match at row 120 so it copies "C120:K120" data but to do this it loses what it had copied from the earlier row. If they are the only two rows then its holding the data from row 120 in memory.

You then run a backwards loop from 500 to 9 and whenever that criteria is matched you're inserting what you copied however you're
a) not telling it where to paste it so its always going to go into the start of the row, if you want it somewhere specific then you'll need to tell it that. eg

Cells(I+1,3).paste

to paste it into column C onwards.

b) though you're always going to paste in the values from the last row that was copied so if you've got 10 matching rows then all of them are going to get the details from the 10th one pasted in.

As John posted the code for, you should do both actions at the same time ie

IF ((x = y) AND (white = black)) THEN
COPY THE DATA
INSERT THE DATA
END IF


You need to post your workbook with a sample sheet showing what is there before and some data showing what you expect.

[nosense]

Its highly valid.

Your code currently runs through 492 rows, from row 9 to row 500 and everytime it finds a matching entry it copies some data...however it can only hold one set of copied data so we'll say it finds a match at row 50, it copies the range "C50: K50" and then moves to the next row and so on....it then finds another match at row 120 so it copies "C120:K120" data but to do this it loses what it had copied from the earlier row. If they are the only two rows then its holding the data from row 120 in memory.

You then run a backwards loop from 500 to 9 and whenever that criteria is matched you're inserting what you copied however you're
a) not telling it where to paste it so its always going to go into the start of the row, if you want it somewhere specific then you'll need to tell it that. eg

Cells(I+1,3).paste

to paste it into column C onwards.

b) though you're always going to paste in the values from the last row that was copied so if you've got 10 matching rows then all of them are going to get the details from the 10th one pasted in.

As John posted the code for, you should do both actions at the same time ie

IF ((x = y) AND (white = black)) THEN
COPY THE DATA
INSERT THE DATA
END IF


You need to post your workbook with a sample sheet showing what is there before and some data showing what you expect.

Thanks, I have posted the workbook, Can you please have a look ?

[JOHN H. DAVIS]

Ditto pjwhitfield try:

Sub nosensez()
For i = 500 To 9 Step -1
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then
    Rows(i + 1).Insert
    Range(Cells(i + 1, 3), Cells(i + 1, 11)).Value = Range(Cells(i, 3), Cells(i, 11)).Value
    End If
Next
End Sub

[nosense]

Ditto pjwhitfield try:

Sub nosensez()
For i = 500 To 9 Step -1
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then
    Rows(i + 1).Insert
    Range(Cells(i + 1, 3), Cells(i + 1, 11)).Value = Range(Cells(i, 3), Cells(i, 11)).Value
    End If
Next
End Sub

This did help but is applying it to condition "As wound" instead of both (AND).

Another big problem is that it does not keep the formulas equivalently for this row....
any way of doing this?
Cheers

[JOHN H. DAVIS]

Can you attach a sample workbook?

[nosense]

1.xlsm

Can you attach a sample workbook?

Here is a sample file.

Sub Run()


Dim tab1 As String, tab2 As String, lastrow As Long

tab1 = Sheets("Machining").Range("f10") & " " & Sheets("Machining").Range("h10")

Sheets(tab1).Select

For i = 200 To 9 Step -1
    If Cells(i, 6) = Sheets("Machining").Range("j10") And Cells(i, 12) = "As wound" Then Rows(i + 1).EntireRow.Insert
 

    Range(Cells(i + 1, 3), Cells(i + 1, 11)).Value = Range(Cells(i, 3), Cells(i, 11)).Value
    
    Range(Cells(i + 1, 16), Cells(i + 1, 21)).Value = Range(Cells(i, 16), Cells(i, 21)).Value
    
    'Cells(i + 1, 12).Value = "Machined"
       
    Next     
End Sub

Seems like only only second "IF" conditions is being accounted. When i add the line

Cells(i + 1, 12).Value = "Machined"

.
It inserts "text" to all rows instead of the one for which condition is met.

I need the new inserted row to keep all formulas too

Cheers,

Stan

[pjwhitfield]

Sorry, Im not seeing the workbook anywhere?