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

 

[Joe4]

If you want to paste it to the next available row, it should probably just be:

    Sheets("Material").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

(as long as column A is always populated in the rows you are copying)

 

[Veni11]

If you want to paste it to the next available row, it should probably just be:

    Sheets("Material").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

(as long as column A is always populated in the rows you are copying)

the column A is always populated that's right. I want that it is pasted after the last row.
So for example in row(10) is PARTXX
Row(11) is free than it paste it in to row(11)
In that case now row(11) is the new last row and the next item that is copied should be pasted to row(12).

Does it work for this scenario with the .Offset(1, 0) ? or do i need something else

 

[Joe4]

the column A is always populated that's right. I want that it is pasted after the last row.
So for example in row(10) is PARTXX
Row(11) is free than it paste it in to row(11)
In that case now row(11) is the new last row and the next item that is copied should be pasted to row(12).

Does it work for this scenario with the .Offset(1, 0) ? or do i need something else

Did you try making the change I suggested and see what happens?
That code find the last row in column A with data, then moves down one row (that is what .Offset(1, 0) does, it moves down one row).

 

[Veni11]

Did you try making the change I suggested and see what happens?
That code find the last row in column A with data, then moves down one row (that is what .Offset(1, 0) does, it moves down one row).

as soon as i copy 2 rows i get en error

"runtime error 1004

You cannot paste this here because the copy area and the paste area are not the same size.

In the paste area, select just a cell or range of the same size and try pasting again."

Is that because of the Offset ? or is in my code something wrong with copying. I don't get the mistake

 

[Joe4]

Do you have any merged cells in the ranges you are copy from or copying to?

 

[Veni11]

Do you have any merged cells in the ranges you are copy from or copying to?

no not at all

 

[Joe4]

Was changing the "27" to "1" the only change you made in that code?
Do you have any protected cells in the ranges you are copying from/pasting to?
Which VBA module have you put this code in?

I think we would need to see that data from each of the two sheets in order to see what is going on, as I see no reason why this would not work.

 

[Veni11]

Was changing the "27" to "1" the only change you made in that code?
Do you have any protected cells in the ranges you are copying from/pasting to?
Which VBA module have you put this code in?

I think we would need to see that data from each of the two sheets in order to see what is going on, as I see no reason why this would not work.

It worked! I made a dumb mistake and changed the rowcount to column J instead of A but now it works perfectly fine! thank you very much!

 

[Joe4]

You are welcome!