Looping problem

[engelwood]

This part of my code has been giving me some problems. All I need it to do is copy Row 2 from Sheet1, paste into Row 2 of Dump (from there the rest of the code runs fine), then I need it to repeat the process for pasting Row 3 of Sheet1 into Row 2 of Dump, Row 4 of Sheet1 into Row 2 of Dump… etc.. However this code just copies Row 2 over and over and over. I need some help.

Do While ActiveCell.Value <> ""
If ActiveCell.Value <> "" Then
Selection.EntireRow.Copy
Sheets("Dump").Select
Range("2:2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select

ElseIf ActiveCell.Value <> "" Then
ActiveCell.Offset(1, 0).Select

End If

Loop

 

[Von Pookie]

That's because you're not incrementing the row number. Your code is looping, but it's being told to paste to the same row every time.

Try this:

<font face=Tahoma><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
i = 2
<SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> ActiveCell.Value <> ""
    <SPAN style="color:#00007F">If</SPAN> ActiveCell.Value <> "" <SPAN style="color:#00007F">Then</SPAN>
        Selection.EntireRow.Copy
        Sheets("Dump").Select
        Rows(i).Select
        ActiveSheet.Paste
        Sheets("Brand to Cross").Select
    <SPAN style="color:#00007F">ElseIf</SPAN> ActiveCell.Value <> "" <SPAN style="color:#00007F">Then</SPAN>
        ActiveCell.Offset(1, 0).Select
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    i = i + 1
<SPAN style="color:#00007F">Loop</SPAN></FONT>

 

[Scott Huish]

As another possibility:

Sub test1()
Dim x As Long
Application.ScreenUpdating = False
For x = 1 To Range("A65536").End(xlUp).Row
Worksheets("Sheet1").Cells(x, 1).EntireRow.Copy
Worksheets("Dump").Cells(x, 1).PasteSpecial
Application.CutCopyMode = False
Next x
Application.ScreenUpdating = True
End Sub

 

[engelwood]

Kristy,

I tired to use your code but it appears that it keeps copying Row 2 of Sheet1 into every row of Dump. I think my original message wasn’t very clear. Let me use different wording:

Row 2, Sheet1 copy to Row 2, Dump
Then…
Row 3, Sheet1 copy to Row 2, Dump
(Yes, copy over what I just pasted in there. I am doing some filtering in between the copying.)
Row 4, Sheet1 copy to Row 2, Dump
Row 5, Sheet1 copy to Row 2, Dump… etc.

 

[engelwood]

Dim i As Integer
i = 2
I modified it to my needs. Here is what I did. Thanks!

Do While ActiveCell.Value <> ""
If ActiveCell.Value <> "" Then
Rows(i).Select
Selection.EntireRow.Copy
Sheets("Dump").Select
Rows("2:2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ElseIf ActiveCell.Value <> "" Then
ActiveCell.Offset(1, 0).Select
End If
i = i + 1
Loop

End Sub