Why this is pasted to row 3 and not 2?

[Tanquen]

I can see why this is pasted to row 3 and not 2 in ws4? I need a loop to go through all the columns A:E in ws1 with values to ws4.

Sub test12()
Dim ws1 As Worksheet
Dim ws4 As Worksheet

Set ws1 = ThisWorkbook.Sheets(1)
Set ws4 = ThisWorkbook.Sheets(4)

ws1.Range("A:E").Rows(2).Copy Destination:=ws4.Range("A" & 2).Rows(2)
End Sub

 

[Joe4]

Your code looks a bit odd, I cannot tell what you are trying to do (ws4.Range("A" & 2).Rows(2) does not really make any sense)
Can you explain, in plain English, exactly what range you are trying to copy and where?

 

[Tanquen]

When I find a value in column C in Sheet1, I need to copy cells A through E from that same row to the same row number in Sheet2.
The script works but it adds an extra row number each time it pastes it into ws4.
So it pastes the correct info but puts the info from sheet1 row 2 on sheet4 row 3 and then sheet1 row 3 on sheet4 row 5 and so on.

 

[Tanquen]

This the full script and I was trying to see why it was adding an empty row each time. So I made a simple script with the rows set to '2' and it still puts them on row 3 on the other sheet4

Sub testcopy2()
Dim ws1 As Worksheet
Dim ws4 As Worksheet
Dim searchValue As String
Dim tagDescriptionValue As String
Dim searchRow As Long
Dim lastRow As Long
Dim i As Long

' Set references to the worksheets
Set ws1 = ThisWorkbook.Sheets(1)
Set ws4 = ThisWorkbook.Sheets(4)

' Find the last row in column C of ws1
lastRow = ws1.Cells(ws1.Rows.Count, "C").End(xlUp).Row

' Loop through column C starting from C2
For i = 2 To lastRow
MsgBox i
' Set searchValue and tagDescriptionValue for each iteration
searchValue = ws1.Cells(i, 3).Value
tagDescriptionValue = ws1.Cells(i, 4).Value

' Copy cells A through E from the specified row in ws1 to ws4
ws1.Range("A:E").Rows(i).Copy Destination:=ws4.Range("A" & i).Rows(i)

' Copy cell F from the specified row in ws1 to cell G in ws4
ws1.Cells(i, 6).Copy Destination:=ws4.Cells(i, 7).Rows(i)
Next i
End Sub

I get this:

Not this:

 

[Tanquen]

So ws1.Range("A:E").Rows(2).Copy Destination:=ws4.Range("A2") ?
This works but then I can't use the 'i' in the For loop.

 

[Tanquen]

Ok, so I was trying to copy to a row? Dropping the '.Rows(i)' seems to work.

ws1.Range("A:E").Rows(i).Copy Destination:=ws4.Range("A" & i)

It don't work for the second part though.

' Copy cell F from the specified row in ws1 to cell G in ws4
ws1.Cells(i, 6).Copy Destination:=ws4.Cells(i, 7)

But this fixes it.

ws4.Cells(i, 7).Value = ws1.Cells(i, 6).Value

 

[jolivanes]

As per your explanation in Post #3. Values only.

Sub With_Loop()
Dim c As Range, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
    For Each c In sh1.Range("C1:C" & sh1.Cells(Rows.Count, 3).End(xlUp).Row)
        If c.Value = "Ola" Then sh2.Cells(c.Row, 1).Resize(, 5).Value = c.Offset(, -2).Resize(, 5).Value
    Next c
End Sub