Looping problem

engelwood

Active Member
Joined
Oct 14, 2004
Messages
327
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
As another possibility:

Code:
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

Active Member
Joined
Oct 14, 2004
Messages
327
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

Active Member
Joined
Oct 14, 2004
Messages
327
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,860
Messages
5,598,491
Members
414,243
Latest member
Shockpulsar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top