VBA Code to copy rows from one sheet to another dynamically

[Veni11]

Hello everyone

I'm trying to copy and paste rows from one sheet to another when a specific item is mentioned in the sheet. The following code is used multiple times for different items. I tried it with offset because i couldn't figure it out how to write the code so that the copied rows are always pasted under the last row without a gap. the sheet "Material" in which the rows are inserted is dynamic. Without the offset i had difficulties because it overwrote the newly inserted rows and didn't recognize the new last row.

I hope someone has some tips. Thank you.

Private Sub Wall()

Dim i As Long, lastrow1 As Long
Dim myname As String

lastrow1 = Sheets("Material").Range("K" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow1
myname = "33527"

Application.ScreenUpdating = False


    If Worksheets("Material").Cells(i, "K").Value = myname Then
    Worksheets("stock").Activate
    Worksheets("stock").Rows("22:25").Copy
    Worksheets("Material").Activate
    Sheets("Material").Range("A" & Rows.Count).End(xlUp).Offset(27, 0).PasteSpecial xlPasteValues
    End If

Application.CutCopyMode = False
Next i

Application.ScreenUpdating = True


End Sub

 

[Veni11]

You are welcome!

May I ask you one last question to this topic. When my main sheet contains the Item number 10 times than code copies the row 10 times in, is there a easy way to change that it doesnt matter how many times in Row K the item is found it should only copy and paste it one time to my sheet..? Sorry and thank you for your feedback

 

[Joe4]

May I ask you one last question to this topic. When my main sheet contains the Item number 10 times than code copies the row 10 times in, is there a easy way to change that it doesnt matter how many times in Row K the item is found it should only copy and paste it one time to my sheet..? Sorry and thank you for your feedback

That is a much bigger more complex issue that would require its own thread.