Copy row from one Excel sheet to another in same workbook - add below do not replace

[seider]

Dear Friends of Excel,

I have a issue I hope that you can help me with.

I have 2 sheets in a workbook - one called ("2. Identification") another called ("4. Main opportunities").

I found below code to copy all the rows marked with "Yes" from ("2. Identification") to ("4. Main opportunities").

Its working fine, the issue is that when i run the macro is it replacing the text. I would like it to add it below a already inserted row. Can you help adding that possibility to the code?

Have tried to search in the forum with no luck.

Please let me know if you want any further clarifications.

seider

Code:
Sub CopyROW()

Set I = Sheets("2. Identification")
Set e = Sheets("4. Main opportunities")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(I.Range("K" & j))

If I.Range("K" & j) = "Yes" Then
d = d + 1
e.Rows(d).Value = I.Rows(j).Value

End If
j = j + 1
Loop
End Sub

 

[Fluff]

Hi & welcome to the board.
Untested, but try

Sub CopyROW()
    
    Dim Isht As Worksheet
    Dim Msht As Worksheet
    Dim Cl As Range
    
    Set Isht = Sheets("2. Identification")
    Set Msht = Sheets("4. Main opportunities")

    For Each Cl In Isht.Range("K2", Isht.Range("K" & Rows.Count).End(xlUp))
        If LCase(Cl.Value) = "yes" Then
            Msht.Range("K" & Rows.Count).End(clup).Offset(1, -10).EntireRow.Value = Cl.EntireRow.Value
        End If
    Next Cl
End Sub

 

[seider]

Hi Fluff,

Thanks - great to be on board.

Sadly it prompts and rune time error on below line

Msht.Range("K" & Rows.Count).End(clup).Offset(1, -10).EntireRow.Value = Cl.EntireRow.Value

 

[Fluff]

oops typo it should be

            Msht.Range("K" & Rows.Count).End([COLOR=#ff0000]xlUp[/COLOR]).Offset(1, -10).EntireRow.Value = Cl.EntireRow.Value

 

[seider]

Fluff, you are the man!

Thank you so much!

 

[Fluff]

Glad to help & thanks for the feedback

 

[seider]

Hi Fluff,

Sorry to bother you again.

When copying to the second sheet is the code inserting it from row 10 and down. I want it to start in row 2 (not 1 as i have headers).

I have tried to change the Offset(1, -10) to make it fit to start in row 2 do you know what im doing wrong?

Thanks again.

seider

Msht.Range("K" & Rows.Count).End(xlUp).Offset(1, -10).EntireRow.Value = Cl.EntireRow.Value

 

[Fluff]

You originally said that you didn't want to overwrite the data. Now you want it to start in row2, which will overwrite the existing data.
Do you want it to start in row 2 every time you run the macro, or to start in the first blank row?

 

[seider]

Hi Fluff,

I still dont want to overwrite the data. When running the macro do i want it to copy the row from the first sheet to the second sheet but when im running the marcro now is it copying it to row 10 and down but I want it start at row 2 after the header and footer.

Please see below images:

https://www.dropbox.com/s/bhhpv3f87f8a4qv/1.PNG?dl=0 (This is sheet 1)
https://www.dropbox.com/s/j3wac90i8te0tjf/2.PNG?dl=0 (This is sheet 2)

Just let me know if you dont understand.

Mike.

 

[Fluff]

In your image of Sheets("4. Main opportunities") you have hidden columns (C:K). I suspect that they are not empty, as the code is looking at col K to find the first blank row.
You can use this

Msht.Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.Value = Cl.EntireRow.Value

Which will use col A to find the first blank row, but any data in the hidden columns will get overwritten.