Macro to copy row to new sheet if string in Column E contains C5

[rkol297]

Hello I need to write a macro that looks at the string in Column E and if it contains "C5" I would like it to copy the entire row to a new sheet. Not all cells in Column E contain values so if it does not I would like the macro to continue to the end of the sheet skipping those empty cells which is what is giving me a problem at this point

 

[Ovisele]

Have you considered deleted the empty rows first using Find and Select, Go To Special, Blanks and after that running your macro? If applicable, of course.

 

[rkol297]

Have you considered deleted the empty rows first using Find and Select, Go To Special, Blanks and after that running your macro? If applicable, of course.

Unfortunately no because other columns contain information for those rows that are blank in column E.

I would like to see a macro that uses column A as the range but looks in column E to see if it contains the letters "C5" in column E and if so then copy to new sheet and loop through all the rows copying only the rows that contain "C5" as part of the information in column E

 

[Joe4]

I would like the macro to continue to the end of the sheet skipping those empty cells which is what is giving me a problem at this point

Does that imply that you already have the code that does the checking and moving?
If so, and you only need to know how to loop through all the rows in column E, you can do something like this:

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column E with data
    lr = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop through all rows in column E
    For r = 1 To lr
'       Check to see if "C5" is in column E of row r
        If InStr(Cells(r, "E"), "C5") > 0 Then
'           Your move code here
        End If
    Next r
    
    Application.ScreenUpdating = True

If you actually need ALL the code, please provide the following information:
- the name of the sheet the data is on
- the name of the sheet you are moving the data to
- whether or not the original row should be deleted from the original sheet

 

[rkol297]

Does that imply that you already have the code that does the checking and moving?
If so, and you only need to know how to loop through all the rows in column E, you can do something like this:

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column E with data
    lr = Cells(Rows.Count, "E").End(xlUp).Row
   
'   Loop through all rows in column E
    For r = 1 To lr
'       Check to see if "C5" is in column E of row r
        If InStr(Cells(r, "E"), "C5") > 0 Then
'           Your move code here
        End If
    Next r
   
    Application.ScreenUpdating = True

If you actually need ALL the code, please provide the following information:
- the name of the sheet the data is on
- the name of the sheet you are moving the data to
- whether or not the original row should be deleted from the original sheet

- the name of the sheet the data is on = turnfourteen
- the name of the sheet you are moving the data to = C5
- whether or not the original row should be deleted from the original sheet = Data should remain just be copied to the new sheet.

 

[Joe4]

Assuming that there aren't any blank cells in column A in the middle of your data, this should work:

Sub MyRowCopy()
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Specify worksheets
    Set ws1 = Sheets("turnfourteen")
    Set ws2 = Sheets("C5")
    
'   Find last row in column E with data
    lr = ws1.Cells(Rows.Count, "E").End(xlUp).Row
    
'   Loop through all rows in column E
    For r = 1 To lr
'       Check to see if "C5" is in column E of row r
        If InStr(ws1.Cells(r, "E"), "C5") > 0 Then
'           Copy row to next sheet
            ws1.Rows(r).Copy ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next r
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub