Thread: Searching Columns and Copying the respective Rows

Erenagh,

I revamped the code. Give this a try:

Sub FindCopyMacro_for_Erenagh()
    
    Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
    Dim ws3 As Worksheet: Set ws3 = Sheets("Sheet3")
    
    Application.ScreenUpdating = False
    
    ws2.UsedRange.Offset(1, 0).ClearContents
    ws3.UsedRange.Offset(1, 0).ClearContents
    
    Dim z As Long
    For z = 4 To ws1.Cells(Rows.Count, "J").End(xlUp).Row
        If LCase(ws1.Cells(z, "J").Value) = "step" Then
            ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
        ElseIf LCase(ws1.Cells(z, "J").Value) = "scep" Then
            ws3.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
        End If
    Next
    
    Application.ScreenUpdating = True
    
End Sub

Hope that helps,
~tigeravatar

Erenagh,

You would change the For z = # to ... in the this line:

    For z = 4 To ws1.Cells(Rows.Count, "J").End(xlUp).Row

That # is the row that the macro starts on. Just change that to change the starting row.

~tigeravatar

Thanks for the response! What I meant was where it printed on the other sheet like sheet 2. So if I wanted any information from sheet 1 to print on sheet 2 row 15 or 20. Depending on what I have going on.

Originally Posted by tigeravatar

Erenagh,

You would change the For z = # to ... in the this line:

    For z = 4 To ws1.Cells(Rows.Count, "J").End(xlUp).Row

That # is the row that the macro starts on. Just change that to change the starting row.

~tigeravatar

This part of the code (and same for ws3.Range...):

ws2.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)....

That code sets the destination row to the next empty line in the sheet. You could instead create a couple of variables to set the range to whatever you'd like. It would look like the following (destination is set to row 15 in the example, and proceeds from there)

Sub FindCopyMacro_for_Erenagh()
    
    Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
    Dim ws3 As Worksheet: Set ws3 = Sheets("Sheet3")
    
    Application.ScreenUpdating = False
    
    ws2.UsedRange.Offset(1, 0).ClearContents
    ws3.UsedRange.Offset(1, 0).ClearContents
    
    Dim ws2DestRow As Long: ws2DestRow = 15
    Dim ws3DestRow As Long: ws3DestRow = 15
    Dim z As Long
    For z = 4 To ws1.Cells(Rows.Count, "J").End(xlUp).Row
        If LCase(ws1.Cells(z, "J").Value) = "step" Then
            ws2.Range("A" & ws2DestRow).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
            ws2DestRow = ws2DestRow + 1
        ElseIf LCase(ws1.Cells(z, "J").Value) = "scep" Then
            ws3.Range("A" & ws3DestRow).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
            ws3DestRow = ws3DestRow + 1
        End If
    Next
    
    Application.ScreenUpdating = True
    
End Sub

Hope this helps,
~tigeravatar

Erenagh,

The code looks fine to me. I'd have to see a sample workbook experiencing the issue to find out what the problem is. Can you upload one?

~tigeravatar

Here is the code that I am running. If you run the macro you can see that if you manually filter there are more results and some results aren't being placed where they should be.

Erenagh,

Your data table had cells with wrap text which was causing extraneous characters within the cells (like carriage returns or spaces). I changed the for next loop to the following and verified that it is now returning all the data correctly:

    For z = 4 To ws1.Cells(Rows.Count, "J").End(xlUp).Row
        If Trim(LCase(ws1.Cells(z, "J").Value)) = "step" Then
            ws2.Range("C" & ws2DestRow).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
            ws2DestRow = ws2DestRow + 1
        ElseIf Trim(LCase(ws1.Cells(z, "J").Value)) = "scep" Then
            ws2.Range("A" & ws3DestRow).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
            ws3DestRow = ws3DestRow + 1
        ElseIf Trim(LCase(ws1.Cells(z, "J").Value)) = "scep-ce" Then
            ws2.Range("A" & ws4DestRow).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
            ws4DestRow = ws4DestRow + 1
        ElseIf Trim(LCase(ws1.Cells(z, "J").Value)) = "co-op" Then
            ws2.Range("A" & ws5DestRow).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
            ws5DestRow = ws5DestRow + 1
        ElseIf Trim(LCase(ws1.Cells(z, "J").Value)) = "paq" Then
            ws3.Range("A" & ws6DestRow).EntireRow.Value = ws1.Cells(z, "J").EntireRow.Value
            ws6DestRow = ws6DestRow + 1
        End If
   
    Next

Hope that helps.
~tigeravatar