Lelkes
06-10-2017, 05:08 PM
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
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