Copy Row Error

[johnnyb5]

I'm trying to copy the entire rows from a spreadsheet called "RAW" to another blank Spreadsheet named "FMX DUIC" The rows that I'm trying to copy are based on the value in the Cell in column "A" (Currently I have three criteria but I may add more in the future). The code appears to have an error(s) in the line that presents the instruction to "cell.entirerow copy"(as I can't get past that point). Would anyone have suggestions on how to fix the code??

ActiveSheet.Name = "RAW"
ActiveWindow.Zoom = 80
Sheets("RAW").Activate
Sheets(1).Select
Worksheets.Add
ActiveSheet.Name = "FMX DUIC"
ActiveWindow.Zoom = 80
Sheets("RAW").Select
Range("A1").Select

Dim bottomC As Long

bottomC = Range("A" & Rows.Count).End(xlUp).Row
Dim cell As Range
For Each cell In Range("A2:A" & bottomC)
If cell.Value = "W0H989" Or cell.Value = "W1D241" Or cell.Value = "W1D261" Then
cell.EntireRow.Copy Sheets("FMX DUIC").Cells(r, 1)
r = r + 1
End If
Next cell

Thanks,
JB

 

[Peter_SSs]

Would anyone have suggestions on how to fix the code??

When you run the code, the initial value of r is zero. There is no Cells(0,1) on the destination sheet.
Set r=1 before the loop or move the r = r + 1 just above the copy line.

 

[johnnyb5]

Awesome Peter,

I couldn't get the "Set" to work but I took your idea and modified the "r" in the first "Cells" command to "1+r" and it copied everything that I needed it to!!!

cell.EntireRow.Copy Sheets("FMX DUIC").Cells("1" + r, 1)

Thanks, Thanks, and Thanks Again,
JB

 

[Peter_SSs]

I couldn't get the "Set" to work ...

The 'Set' was not meant to be a vba code word, just English.
So this is what I meant by that bit

Dim cell As Range
r = 1    <- This line inserted
For Each cell In Range("A2:A" & bottomC)
If cell.Value = "W0H989" Or cell.Value = "W1D241" Or cell.Value = "W1D261" Then
cell.EntireRow.Copy Sheets("FMX DUIC").Cells(r, 1)
r = r + 1
End If
Next cell

0r

Dim cell As Range
For Each cell In Range("A2:A" & bottomC)
If cell.Value = "W0H989" Or cell.Value = "W1D241" Or cell.Value = "W1D261" Then
r = r + 1                                                  <- These lines swapped
cell.EntireRow.Copy Sheets("FMX DUIC").Cells(r + 1, 1)     <- These lines swapped
End If
Next cell

.. but I took your idea and modified the "r" in the first "Cells" command to "1+r" and it copied everything that I needed it to!!!

cell.EntireRow.Copy Sheets("FMX DUIC").Cells("1" + r, 1)

Thanks, Thanks, and Thanks Again,
JB

Not that important but you shouldn't need those red quote marks.

 

[johnnyb5]

Pete,
I worked with your suggestions over the weekend and they were right on the mark. I removed my "addition" solution and decided to use your "line swap" solution. After trying the different options I added the criteria for further requirements and didn't have any problems searching across two columns for 14 criteria. The macro is excellent!

Thanks Again!!!
JB

 

[Peter_SSs]

Pete,
I worked with your suggestions over the weekend and they were right on the mark. I removed my "addition" solution and decided to use your "line swap" solution. After trying the different options I added the criteria for further requirements and didn't have any problems searching across two columns for 14 criteria. The macro is excellent!

Thanks Again!!!
JB

Good news. Glad you have made progress. Thanks for letting us know.