paste every time on the same row

[Lelkes]

Hi

I´ve problem with the code below

 Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet, i As Long
Set ws1 = Sheet1
Set ws2 = Sheet2
'lastrow
Dim lRw As Long
lRw = ws1.Cells(Rows.Count, 1).End(xlUp).Row
'first empty row
Dim eRow As Long
eRow = ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


For i = 2 To lRw
ws1.Cells(i, 1).Copy ws2.Cells(eRow, 3)
ws1.Cells(i, 6).Copy ws2.Cells(eRow, 1)
ws1.Cells(i, 7).Copy ws2.Cells(eRow, 2)
ws1.Cells(i, 8).Copy ws2.Cells(eRow, 4)
ws1.Cells(i, 9).Copy ws2.Cells(eRow, 5)
ws1.Cells(i, 10).Copy ws2.Cells(eRow, 6)
Next i


ws2.Columns.AutoFit


End Sub

What I want is this:
i=2 first empty row (=eRow) is 4 and paste on this row
i=3 first empty row (=eRow) is 5 and paste on this row
i=4 first empty row (=eRow) is 6 and paste on this row
i=5 first empty row (=eRow) is 7 and paste on this row
i=6 first empty row (=eRow) is 8 and paste on this row

But when I debug (F8) the i increases with 1, but eRow remains the same (=4) -> everytime i increase the paste is on the same row
I hope someone in this forum has the solution to my problem


Thanks in advance for your time and help
Regards
Peter
- - - - - - - - - - - - - -
excel 2016
windows 10

[Logit]

.
Try this :

Option Explicit


Sub test()
    Dim ws1 As Worksheet, ws2 As Worksheet, i As Long
    Set ws1 = Sheet1
    Set ws2 = Sheet2
     'lastrow
    Dim lRw As Long
    lRw = ws1.Cells(Rows.Count, 1).End(xlUp).Row
     'first empty row
    Dim eRow As Long
    'eRow = ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
     
     
    For i = 2 To lRw
    eRow = ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ws1.Cells(i, 1).Copy ws2.Cells(eRow, 1)
        ws1.Cells(i, 6).Copy ws2.Cells(eRow, 2)
        ws1.Cells(i, 7).Copy ws2.Cells(eRow, 3)
        ws1.Cells(i, 8).Copy ws2.Cells(eRow, 4)
        ws1.Cells(i, 9).Copy ws2.Cells(eRow, 5)
        ws1.Cells(i, 10).Copy ws2.Cells(eRow, 6)
    Next i
     
     
    ws2.Columns.AutoFit
     
     
End Sub

[Lelkes]

Thanks for your help!
of course it works with your solution!
I´m trying to learn and improve, so could you please explain why it works if the line is moved ?

Regards
Peter

[Logit]

.
Loop.jpg

Think in terms of a loop. (Although it isn't really a loop in the traditional sense - at least not in my mind)

For i = 2 to lRw (last row)
Blah
Blah
Blah
Next i

Start at the 2nd row ... do some stuff ... then go back to the beginning (Next i) and do it again and again until you've reached the (last row) of data. When you reach the last row, get out of the loop
and move on to the next line of code : ws2.Columns.AutoFit


If you leave this line out of the "loop" : eRow = ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row it will consider the command in the line of code once, go into the "loop" (For i = 2 to lRw (last row)) and execute the
rest of the code, never again 'thinking' about needing to paste the next new data on the next empty row.

By placing this line inside the 'loop' : eRow = ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row the code is reminded each time to look for the next blank row for pasting new data.


Hope that makes sense.

[Lelkes]

Thanks!
makes perfect sense! And even a noob as me understand

Regards
Peter

[Logit]

You are welcome.