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