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

rkol297

Board Regular
Joined
Nov 12, 2010
Messages
131
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Ovisele

New Member
Joined
Apr 28, 2015
Messages
21
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

Board Regular
Joined
Nov 12, 2010
Messages
131
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
    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

Board Regular
Joined
Nov 12, 2010
Messages
131
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:
VBA Code:
    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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Assuming that there aren't any blank cells in column A in the middle of your data, this should work:
VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,502
Messages
5,636,696
Members
416,935
Latest member
Atulcp

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