VBA Rows.Copy Problem

[beartooth91]

Not sure what I'm doing wrong here, its only pasting the last entry. There should be 6 lines pasted.

Sub ForEa_Test()
'
With Worksheets("Combined")
  Dim Lrw As Long
  Lrw = .Cells(Rows.Count, "B").End(xlUp).Row

  For Each cell In .Range("B8:N" & Lrw)
    If cell.Value = "DI" Then
       cell.EntireRow.Copy Destination:=Sheets("New2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End If
  Next cell
End With
End Sub

 

[Logit]

Not certain what is happening there ... but ... your macro works here.

???

 

[beartooth91]

Not certain what is happening there ... but ... your macro works here.

???

That's strange. If I change the .Copy piece to Interior.ColorIndex = 6; it highlights all 6 entries on the source worksheet. When I put .Copy/Destination back in; it only pastes the last row entry.

 

[Logit]

I don't know what to suggest. Sorry.

 

[Fluff]

Is there anything in col A?

 

[beartooth91]

Is there anything in col A?

Screenshot of the worksheet I'm copying from.
Changed the code to the below and it does exactly the same thing as the previous version....

Sub ForEa_Test()
'
With Worksheets("Combined")
  Dim Lrw As Long, i As Long, cell As Variant
  Lrw = .Cells(Rows.Count, "B").End(xlUp).Row

  '
  For i = 8 To Lrw
    If .Cells(i, "E").Value = "DI" Then
       .Cells(i, "E").EntireRow.Interior.ColorIndex = 34
       .Cells(i, "E").EntireRow.Copy Destination:=Sheets("New2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End If
  Next i
End With
End Sub

 

[maxpit]

Hi *beartooth91

try this modified code:

Sub ForEa_Test()
'
    Dim cell As Range                                                                                     ' <<< added
    Dim nl As Long                                                                                        ' <<< added
    
    With Worksheets("Combined")
        Dim Lrw As Long
        Lrw = .Cells(Rows.Count, "B").End(xlUp).Row
        
        For Each cell In .Range("B8:N" & Lrw)
            If cell.Value = "DI" Then
                nl = Sheets("New2").Cells(Rows.Count, 2).End(xlUp).Row + 1                                     ' <<< added
'                cell.EntireRow.Copy Destination:=Sheets("New2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)   ' <<< deleted
                cell.EntireRow.Copy Destination:=Sheets("New2").Rows(nl)                                       ' <<< added
            End If
        Next cell
    End With
End Sub

and let us know

Bye

 

[Fluff]

There is nothing in col A so this line

Sheets("New2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

will always copy the data to row 2. You need to change it to look at col B (ie 2) not col A.

Sheets("New2").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)

 

[beartooth91]

Hi *beartooth91

try this modified code:

Sub ForEa_Test()
'
    Dim cell As Range                                                                                     ' <<< added
    Dim nl As Long                                                                                        ' <<< added
   
    With Worksheets("Combined")
        Dim Lrw As Long
        Lrw = .Cells(Rows.Count, "B").End(xlUp).Row
       
        For Each cell In .Range("B8:N" & Lrw)
            If cell.Value = "DI" Then
                nl = Sheets("New2").Cells(Rows.Count, 2).End(xlUp).Row + 1                                     ' <<< added
'                cell.EntireRow.Copy Destination:=Sheets("New2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)   ' <<< deleted
                cell.EntireRow.Copy Destination:=Sheets("New2").Rows(nl)                                       ' <<< added
            End If
        Next cell
    End With
End Sub

and let us know

Bye

This worked. Thanks. The only problem is I'm not understanding why my version didn't work. Your solution agrees with Fluff's post......I'm just not really understanding what Column A has to do with it......?

 

[maxpit]

in this snippet of code

nl = Sheets("New2").Cells(Rows.Count, 2).End(xlUp).Row + 1                                     ' <<< added

with Cells(Rows.Count, 2) I read which is the first free cell of column B (2) because in your example A (1) is empty.

Then with "Row" I apply that row number to the copy