Copy Data

[billandrew]

Hello All

I know this is incorrect , Can someone explain why all my data which meets the criteria <> 4 is not copied to sheet 7. Is the rows(j) the issue

Sub CopyNot4Q()
    
        Dim lr1 As Long, p As Long
        Dim ws As Worksheet
        
        Set ws = Sheet6
        
        ws.Activate
        
        lr1 = ws.Range("B" & Rows.Count).End(xlUp).Row
        
        For p = 2 To lr1
        
        
        If Left(Cells(p, "B"), 1) <> 4 Then
        
               
        Rows(p).Copy Destination:=Sheets("Sheet7").Range("A1")
        
        End If
        Next p

 

[mumps]

Make Sheet6 the active sheet and try this macro:

Sub CopyNot4Q()
    Application.ScreenUpdating = False
    Dim lr1 As Long, p As Long
    lr1 = Sheets("Sheet6").Range("B" & Rows.Count).End(xlUp).Row
    For p = 2 To lr1
        If Left(Cells(p, "B"), 1) <> 4 Then
            Rows(p).EntireRow.Copy Sheets("Sheet7").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next p
    Application.ScreenUpdating = True
End Sub

 

[billandrew]

Thank you Mumps.

Was the error due to the missing entirerow.copy

 

[mumps]

You are very welcome. This line of code:

Set ws = Sheet6

should have read:

 ws = Sheets("Sheet6")

Therefore lr1 had no value. Also, this line:

 Rows(p).Copy Destination:=Sheets("Sheet7").Range("A1")

would have copied each row on top of each other at cell A1 whereas this line:

Rows(p).EntireRow.Copy Sheets("Sheet7").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

copies the row below the last row which has data. I hope that makes sense.

 

[billandrew]

Great.

Thanks again.....

 

[mumps]

My pleasure.